Delen via

BULK INSERT of OPENROWSET(BULK...) gebruiken om gegevens te importeren in SQL Server

  • Artikel

van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Dit artikel bevat een overzicht van het gebruik van de instructie Transact-SQL BULK INSERT en insert... SELECT * FROM OPENROWSET(BULK...)-instructie om gegevens bulksgewijs uit een gegevensbestand te importeren in een SQL Server- of Azure SQL Database-tabel. In dit artikel worden ook beveiligingsoverwegingen beschreven voor het gebruik van BULK INSERT en OPENROWSET (BULK...) en het gebruik van deze methoden om bulksgewijs vanuit een externe gegevensbron te importeren.

Notitie

Wanneer u BULK INSERT of OPENROWSET(BULK...) gebruikt, is het belangrijk om te begrijpen hoe de SQL Server-versie imitatie afhandelt. Zie 'Beveiligingsoverwegingen' verderop in dit onderwerp voor meer informatie.

BULK INSERT-instructie

BULK INSERT laadt gegevens uit een gegevensbestand in een tabel. Deze functionaliteit is vergelijkbaar met die van de in optie van de opdracht bcp; het gegevensbestand wordt echter gelezen door het SQL Server-proces. Zie BULK INSERT (Transact-SQL)voor een beschrijving van de syntaxis van BULK INSERT.

VOORBEELDEN VAN BULK INSERT

OPENROWSET(BULK...) Functie

De OPENROWSET-provider voor bulkrijsets wordt geopend door de functie OPENROWSET aan te roepen en de optie BULK op te geven. Met de functie OPENROWSET(BULK...) hebt u toegang tot externe gegevens door verbinding te maken met een externe gegevensbron, zoals een gegevensbestand, via een OLE DB-provider.

Als u gegevens bulksgewijs wilt importeren, roept u OPENROWSET(BULK...) aan vanuit een SELECT... FROM-component binnen een INSERT-instructie. De basissyntaxis voor het bulksgewijs importeren van gegevens is:

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

Bij gebruik in een INSERT-instructie ondersteunt OPENROWSET(BULK...) tabelhints. Naast de reguliere tabelhints, zoals TABLOCK, kan de BULK-component de volgende gespecialiseerde tabelhints accepteren: IGNORE_CONSTRAINTS (negeert alleen de CONTROLEbeperkingen), IGNORE_TRIGGERS, KEEPDEFAULTS en KEEPIDENTITY. Zie tabelhints (Transact-SQL)voor meer informatie.

Zie OPENROWSET (Transact-SQL)voor meer informatie over aanvullende toepassingen van de optie BULK.

INVOEGEN... SELECT * FROM OPENROWSET(BULK...)-instructies - voorbeelden

Beveiligingsoverwegingen

Als een gebruiker een SQL Server-aanmelding gebruikt, wordt het beveiligingsprofiel van het SQL Server-procesaccount gebruikt. Een aanmelding met SQL Server-authenticatie kan niet worden geverifieerd buiten de Database Engine. Wanneer een BULK INSERT-opdracht wordt gestart door een aanmelding met behulp van SQL Server-verificatie, wordt de verbinding met de gegevens gemaakt met behulp van de beveiligingscontext van het SQL Server-procesaccount (het account dat wordt gebruikt door de SQL Server Database Engine-service).

Als u de brongegevens wilt lezen, moet u het account dat door de SQL Server Database Engine wordt gebruikt, toegang verlenen tot de brongegevens. Als een SQL Server-gebruiker zich daarentegen aanmeldt met Windows-verificatie, kan de gebruiker alleen de bestanden lezen die toegankelijk zijn voor het gebruikersaccount, ongeacht het beveiligingsprofiel van het SQL Server-proces.

Denk bijvoorbeeld aan een gebruiker die zich heeft aangemeld bij een exemplaar van SQL Server met behulp van Windows-verificatie. Als de gebruiker BULK INSERT of OPENROWSET kan gebruiken om gegevens uit een gegevensbestand te importeren in een SQL Server-tabel, heeft het gebruikersaccount leestoegang tot het gegevensbestand nodig. Met toegang tot het gegevensbestand kan de gebruiker gegevens uit het bestand importeren in een tabel, zelfs als het SQL Server-proces geen toegang heeft tot het bestand. De gebruiker hoeft geen machtiging voor bestandstoegang te verlenen voor het SQL Server-proces.

SQL Server en Microsoft Windows kunnen zo worden geconfigureerd dat een exemplaar van SQL Server verbinding kan maken met een ander exemplaar van SQL Server door de referenties van een geverifieerde Windows-gebruiker door te sturen. Deze regeling staat bekend als imitatie of delegering. Inzicht in hoe SQL Server-versies de beveiliging afhandelen voor gebruikersimitatie is belangrijk wanneer u BULK INSERT of OPENROWSET gebruikt. Door gebruikersimitatie kan het gegevensbestand zich op een andere computer bevinden dan het SQL Server-proces of de gebruiker. Als een gebruiker op Computer_A bijvoorbeeld toegang heeft tot een gegevensbestand op Computer_Ben de overdracht van referenties op de juiste manier is ingesteld, kan de gebruiker verbinding maken met een exemplaar van SQL Server dat wordt uitgevoerd op Computer_C, toegang tot het gegevensbestand op Computer_B, en gegevens uit dat bestand bulksgewijs importeren in een tabel op Computer_C.

Bulksgewijs importeren naar SQL Server vanuit een extern gegevensbestand

BULK INSERT of INSERT...SELECT * FROM OPENROWSET(BULK...) gebruiken om gegevens bulk te importeren vanaf een andere computer, moet het gegevensbestand gedeeld worden tussen de twee computers. Als u een gedeeld gegevensbestand wilt opgeven, gebruikt u de UNC-naam (Universal Naming Convention), die de algemene vorm heeft, \\Servernaam\Sharename\Pad\Bestandsnaam. Daarnaast moet het account dat wordt gebruikt voor toegang tot het gegevensbestand beschikken over de machtigingen die vereist zijn voor het lezen van het bestand op de externe schijf.

Met de volgende BULK INSERT instructie worden gegevens bijvoorbeeld bulksgewijs geïmporteerd in de SalesOrderDetail tabel van de AdventureWorks-database uit een gegevensbestand met de naam newdata.txt. Dit gegevensbestand bevindt zich in een gedeelde map met de naam \dailyorders in een netwerksharemap met de naam salesforce op een systeem met de naam computer2.

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

Notitie

Deze beperking is niet van toepassing op het hulpprogramma bcp omdat de client het bestand onafhankelijk van SQL Server leest.

Bulksgewijs importeren vanuit Azure Blob Storage

Wanneer u gegevens importeert uit Azure Blob Storage en de gegevens niet openbaar zijn (anonieme toegang), maakt u een DATABASE SCOPED CREDENTIAL op basis van een SAS-sleutel die is versleuteld met een MASTER KEYen maakt u vervolgens een externe databasebron voor gebruik in de opdracht BULK INSERT.

U kunt ook een DATABASE SCOPED CREDENTIAL maken op basis van MANAGED IDENTITY om aanvragen voor gegevenstoegang in niet-openbare opslagaccounts te autoriseren. Wanneer u MANAGED IDENTITYgebruikt, moet Azure Storage machtigingen aan de beheerde identiteit van het exemplaar geven door de ingebouwde Azure-rol Storage Blob Data Contributor toe te voegen, die lees-/schrijftoegang biedt aan deze beheerde identiteit voor de benodigde Azure Blob Storage-containers. Azure SQL Managed Instance heeft een door het systeem toegewezen beheerde identiteit en kan ook een of meer door de gebruiker toegewezen beheerde identiteiten hebben. U kunt door het systeem toegewezen beheerde identiteiten of door de gebruiker toegewezen beheerde identiteiten gebruiken om de aanvragen te autoriseren. Voor autorisatie wordt de default identiteit van het beheerde exemplaar gebruikt (dat is de primaire door de gebruiker toegewezen beheerde identiteit of door het systeem toegewezen beheerde identiteit als door de gebruiker toegewezen beheerde identiteit niet is opgegeven).

Belangrijk

Beheerde identiteit is alleen van toepassing op Azure SQL. SQL Server biedt geen ondersteuning voor managed identity.

Notitie

Gebruik geen expliciete transactie of u ontvangt een 4861-fout.

BULK INSERT gebruiken

In het volgende voorbeeld ziet u hoe u de opdracht BULK INSERT gebruikt om gegevens uit een CSV-bestand te laden in een Azure Blob Storage-locatie waarop u een SAS-sleutel hebt gemaakt. De Azure Blob Storage-locatie is geconfigureerd als een externe gegevensbron. Hiervoor is een database-gerichte referentie vereist met behulp van een gedeelde toegangssignatuur die is versleuteld met een hoofdsleutel in de gebruikersdatabase.

--> 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');

In het volgende voorbeeld ziet u hoe u de opdracht BULK INSERT gebruikt om gegevens uit een CSV-bestand in een Azure Blob Storage-locatie te laden met behulp van beheerde identiteit. De Azure Blob Storage-locatie is geconfigureerd als een externe gegevensbron.

--> 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');

Belangrijk

Beheerde identiteit is alleen van toepassing op Azure SQL. SQL Server biedt geen ondersteuning voor managed identity.

Azure SQL Database biedt geen ondersteuning voor het lezen van Windows-bestanden.

OPENROWSET gebruiken

In het volgende voorbeeld ziet u hoe u de opdracht OPENROWSET gebruikt om gegevens uit een CSV-bestand te laden in een Azure Blob Storage-locatie waarop u een SAS-sleutel hebt gemaakt. De Azure Blob Storage-locatie is geconfigureerd als een externe gegevensbron. Hiervoor zijn database-inloggegevens vereist met een gedeelde toegangshandtekening (Shared Access Signature) die is versleuteld met een hoofdsleutel in de gebruikersdatabase.

--> 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;

Belangrijk

Azure SQL Database biedt geen ondersteuning voor het lezen van Windows-bestanden.

Verwante inhoud