Dela via

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

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

Den här artikeln innehåller en översikt över hur du använder Transact-SQL-instruktionen BULK INSERT och -instruktionen INSERT...SELECT * FROM OPENROWSET(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 BULK INSERT och OPENROWSET(BULK...)och användning av dessa metoder för massimport från en fjärrdatakälla.

BULK INSERT-instruktion

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

BULK INSERT-exempel

OPENROWSET(BULK...) Funktion

Leverantören av bulkdatamängd OPENROWSET nås genom att anropa funktionen OPENROWSET och ange alternativet BULK. Med OPENROWSET(BULK...) funktionen kan du komma åt fjärrdata genom att ansluta till en fjärrdatakälla, till exempel en datafil, via en dataprovider.

Om du vill massimportera data, anropar du OPENROWSET(BULK...) från en SELECT...FROM-sats inom en INSERT-instruktion.

Den grundläggande syntaxen för massimport av data är:

INSERT ... SELECT * FROM OPENROWSET(BULK...)

När det används i en INSERT-instruktion, har OPENROWSET(BULK...) stöd för tabelltips. Förutom vanliga tabelltips, till exempel TABLOCK, BULK kan satsen acceptera följande specialiserade tabelltips:

  • IGNORE_CONSTRAINTS (ignorerar endast CHECK-begränsningarna)
  • IGNORE_TRIGGERS
  • KEEPDEFAULTS
  • KEEPIDENTITY

Mer information finns i Tabelltips (Transact-SQL).

Information om ytterligare användning av BULK-alternativet finns i OPENROWSET BULK (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

Om du vill använda BULK INSERT eller INSERT...SELECT * FROM OPENROWSET(BULK...) 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';

Anteckning

Den här begränsningen bcp gäller inte för verktyget 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 CREATE DATABASE SCOPED CREDENTIAL baserat på en SAS-nyckel som är krypterad med en CREATE MASTER KEY och skapar sedan en extern databaskälla för användning i kommandot BULK INSERT .

Du kan också skapa en CREATE 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 stöds inte i SQL Server-versioner före SQL Server 2025.

Anteckning

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

Använd MASSINFOGNING

I följande exempel visas hur du använder BULK INSERT kommandot 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 BULK INSERT kommandot 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

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

Använda OPENROWSET

I följande exempel visas hur du använder OPENROWSET kommandot 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