Dela via

Använd BULK INSERT eller OPENROWSET(BULK...) för att importera data till SQL Server

  • Artikel

gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Den här artikeln innehåller en översikt över hur du använder instruktionen Transact-SQL BULK INSERT och INSERT... VÄLJ * FRÅN OPENROWSET-instruktionen (BULK...) för att massimportera data från en datafil till en SQL Server- eller Azure SQL Database-tabell. I den här artikeln beskrivs även säkerhetsöverväganden för användning av BULK INSERT och OPENROWSET(BULK...) och användning av dessa metoder för massimport från en fjärrdatakälla.

Anteckning

När du använder BULK INSERT eller OPENROWSET(BULK...) är det viktigt att förstå hur SQL Server-versionen hanterar efterlikning. Mer information finns i "Säkerhetsöverväganden" senare i det här avsnittet.

BULK INSERT-instruktion

BULK INSERT läser in data från en datafil till en tabell. Den här funktionen liknar den som tillhandahålls av i alternativet för kommandot bcp. Datafilen läse dock av SQL Server-processen. En beskrivning av BULK INSERT-syntaxen finns i BULK INSERT (Transact-SQL).

BULK INSERT-exempel

OPENROWSET(BULK...) Funktion

OPENROWSET-bulk-raduppsättningsprovidern nås genom att anropa funktionen OPENROWSET och specificera BULK-alternativet. Med funktionen OPENROWSET(BULK...) kan du komma åt fjärrdata genom att ansluta till en fjärrdatakälla, till exempel en datafil, via en OLE DB-provider.

Om du vill massimportera data anropar du OPENROWSET(BULK...) från en SELECT... FROM-sats i en INSERT-instruktion. Den grundläggande syntaxen för massimport av data är:

INFOGA ... VÄLJ * FRÅN OPENROWSET(BULK...)

När den används i en INSERT-instruktion stöder OPENROWSET(BULK...) tabelltips. Förutom vanliga tabelltips, till exempel TABLOCK, kan BULK-satsen acceptera följande specialiserade tabelltips: IGNORE_CONSTRAINTS (ignorerar endast CHECK-begränsningarna), IGNORE_TRIGGERS, KEEPDEFAULTS och KEEPIDENTITY. Mer information finns i Tabelltips (Transact-SQL).

Information om ytterligare användning av BULK-alternativet finns i OPENROWSET (Transact-SQL).

INFOGA... SELECT * FROM OPENROWSET(BULK...) satser - exempel

Säkerhetshänsyn

Om en användare använder en SQL Server-inloggning används säkerhetsprofilen för SQL Server-processkontot. En inloggning med SQL Server-autentisering kan inte autentiseras utanför databasmotorn. När ett BULK INSERT-kommando initieras av en inloggning med SQL Server-autentisering görs därför anslutningen till data med hjälp av säkerhetskontexten för SQL Server-processkontot (det konto som används av SQL Server Database Engine-tjänsten).

Om du vill läsa källdata måste du bevilja det konto som används av SQL Server Database Engine åtkomst till källdata. Om en SQL Server-användare däremot loggar in med hjälp av Windows-autentisering kan användaren bara läsa de filer som kan nås av användarkontot, oavsett säkerhetsprofilen för SQL Server-processen.

Tänk dig till exempel en användare som loggade in på en instans av SQL Server med hjälp av Windows-autentisering. För att användaren ska kunna använda BULK INSERT eller OPENROWSET för att importera data från en datafil till en SQL Server-tabell kräver användarkontot läsåtkomst till datafilen. Med åtkomst till datafilen kan användaren importera data från filen till en tabell även om SQL Server-processen inte har behörighet att komma åt filen. Användaren behöver inte bevilja filåtkomstbehörighet till SQL Server-processen.

SQL Server och Microsoft Windows kan konfigureras så att en instans av SQL Server kan ansluta till en annan instans av SQL Server genom att vidarebefordra autentiseringsuppgifterna för en autentiserad Windows-användare. Det här arrangemanget kallas förklädnad eller delegering. Det är viktigt att förstå hur SQL Server-versionen hanterar säkerhet för användarpersonifiering när du använder BULK INSERT eller OPENROWSET. Användarpersonifiering gör att datafilen kan finnas på en annan dator än sql server-processen eller användaren. Om en användare på Computer_A till exempel har åtkomst till en datafil på Computer_Boch delegeringen av autentiseringsuppgifter har angetts på rätt sätt kan användaren ansluta till en instans av SQL Server som körs på Computer_C, komma åt datafilen på Computer_B, och massimportera data från filen till en tabell på Computer_C.

Massimport till SQL Server från en fjärrdatafil

Så här använder du BULK INSERT eller INSERT... VÄLJ * FRÅN OPENROWSET(BULK...) för att massimportera data från en annan dator måste datafilen delas mellan de två datorerna. Om du vill ange en delad datafil använder du dess unc-namn (Universal Naming Convention), som har det allmänna formuläret, \\Servername\Sharename\Path\Filename. Dessutom måste det konto som används för att komma åt datafilen ha de behörigheter som krävs för att läsa filen på fjärrdisken.

Följande BULK INSERT-instruktion massimporterar data i SalesOrderDetail-tabellen i AdventureWorks-databasen från en datafil med namnet newdata.txt. Den här datafilen finns i en delad mapp med namnet \dailyorders i en nätverksresurskatalog med namnet salesforce på ett system med namnet computer2.

BULK INSERT AdventureWorks2022.Sales.SalesOrderDetail
   FROM '\\computer2\salesforce\dailyorders\neworders.txt';

Not

Den här begränsningen gäller inte för verktyget bcp eftersom klienten läser filen oberoende av SQL Server.

Massimport från Azure Blob Storage

När du importerar från Azure Blob Storage och data inte är offentliga (anonym åtkomst) skapar du en DATABASE SCOPED CREDENTIAL- baserat på en SAS-nyckel som krypteras med en MASTER KEYoch skapar sedan en extern databaskälla för användning i KOMMANDOT BULK INSERT.

Du kan också skapa en DATABASE SCOPED CREDENTIAL- baserat på MANAGED IDENTITY för att auktorisera begäranden om dataåtkomst i icke-offentliga lagringskonton. När du använder MANAGED IDENTITYmåste Azure Storage bevilja behörigheter till den hanterade identiteten för instansen genom att lägga till den inbyggda Azure-rollen Storage Blob Data Contributor i rollbaserad åtkomstkontroll (RBAC), vilket ger läs-/skrivåtkomst till den hanterade identiteten för de nödvändiga Azure Blob Storage-containrarna. Azure SQL Managed Instance har en systemtilldelad hanterad identitet och kan också ha en eller flera användartilldelade hanterade identiteter. Du kan använda antingen systemtilldelade hanterade identiteter eller användartilldelade hanterade identiteter för att auktorisera begäranden. För auktorisering används den default identiteten för den hanterade instansen (som är primär användartilldelad hanterad identitet eller systemtilldelad hanterad identitet om användartilldelad hanterad identitet inte har angetts).

Viktig

Hanterad identitet gäller endast för Azure SQL. SQL Server stöder inte hanterad identitet.

Obs

Använd inte explicit transaktion, eller så får du ett 4861-fel.

Använda MASSINFOGNING

I följande exempel visas hur du använder KOMMANDOT BULK INSERT för att läsa in data från en csv-fil på en Azure Blob Storage-plats där du har skapat en SAS-nyckel. Azure Blob Storage-platsen är konfigurerad som en extern datakälla. Detta kräver en databasomfattande autentiseringsuppgift med hjälp av en signatur för delad åtkomst som krypteras med hjälp av en huvudnyckel i användardatabasen.

--> Optional - a MASTER KEY is not required if a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword1';
GO
--> Optional - a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
 SECRET = '******srt=sco&sp=rwac&se=2017-02-01T00:55:34Z&st=2016-12-29T16:55:34Z***************';

 -- NOTE: Make sure that you don't have a leading ? in SAS token, and
 -- that you have at least read permission on the object that should be loaded srt=o&sp=r, and
 -- that expiration period is valid (all dates are in UTC time)

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( TYPE = BLOB_STORAGE,
          LOCATION = 'https://****************.blob.core.windows.net/invoices'
          , CREDENTIAL= MyAzureBlobStorageCredential --> CREDENTIAL is not required if a blob is configured for public (anonymous) access!
);

BULK INSERT Sales.Invoices
FROM 'inv-2017-12-08.csv'
WITH (DATA_SOURCE = 'MyAzureBlobStorage');

I följande exempel visas hur du använder KOMMANDOT BULK INSERT för att läsa in data från en csv-fil på en Azure Blob Storage-plats med hjälp av hanterad identitet. Azure Blob Storage-platsen är konfigurerad som en extern datakälla.

--> Optional - a MASTER KEY is not required if a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword1';
GO

--> Optional - a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
WITH IDENTITY = 'Managed Identity';

-- NOTE: Make sure you have granted Storage Bob Data Contributor RBAC on storage to provides read/write access to the managed identity for the necessary Azure Blob Storage containers.

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( TYPE = BLOB_STORAGE,
          LOCATION = 'https://****************.blob.core.windows.net/invoices'
          , CREDENTIAL= MyAzureBlobStorageCredential --> CREDENTIAL is not required if a blob is configured for public (anonymous) access!
);

BULK INSERT Sales.Invoices
FROM 'inv-2017-12-08.csv'
WITH (DATA_SOURCE = 'MyAzureBlobStorage');

Viktig

Hanterad identitet gäller endast för Azure SQL. SQL Server stöder inte hanterad identitet.

Azure SQL Database stöder inte läsning från Windows-filer.

Använda OPENROWSET

I följande exempel visas hur du använder KOMMANDOT OPENROWSET för att läsa in data från en csv-fil på en Azure Blob Storage-plats där du har skapat en SAS-nyckel. Azure Blob Storage-platsen är konfigurerad som en extern datakälla. Detta kräver en databasomfattande autentiseringsuppgift med hjälp av en signatur för delad åtkomst som krypteras med hjälp av en huvudnyckel i användardatabasen.

--> Optional - a MASTER KEY is not required if a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword1';
GO
--> Optional - a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
 SECRET = '******srt=sco&sp=rwac&se=2017-02-01T00:55:34Z&st=2016-12-29T16:55:34Z***************';

 -- NOTE: Make sure that you don't have a leading ? in SAS token, and
 -- that you have at least read permission on the object that should be loaded srt=o&sp=r, and
 -- that expiration period is valid (all dates are in UTC time)

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( TYPE = BLOB_STORAGE,
          LOCATION = 'https://****************.blob.core.windows.net/invoices'
          , CREDENTIAL= MyAzureBlobStorageCredential --> CREDENTIAL is not required if a blob is configured for public (anonymous) access!
);

INSERT INTO achievements with (TABLOCK) (id, description)
SELECT * FROM OPENROWSET(
   BULK  'csv/achievements.csv',
   DATA_SOURCE = 'MyAzureBlobStorage',
   FORMAT ='CSV',
   FORMATFILE='csv/achievements-c.xml',
   FORMATFILE_DATA_SOURCE = 'MyAzureBlobStorage'
    ) AS DataFile;

Viktig

Azure SQL Database stöder inte läsning från Windows-filer.

Relaterat innehåll