Delen via

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

van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Dit artikel bevat een overzicht van het gebruik van de Transact-SQL-instructie BULK INSERT en de INSERT...SELECT * FROM OPENROWSET(BULK...) instructie voor het bulksgewijs importeren van gegevens uit een gegevensbestand in een SQL Server- of Azure SQL Database-tabel.

In dit artikel worden ook beveiligingsoverwegingen beschreven voor het gebruik BULK INSERT en OPENROWSET(BULK...)en het gebruik van deze methoden om bulksgewijs te importeren vanuit een externe gegevensbron.

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 bcp opdracht. Het gegevensbestand wordt echter gelezen door het SQL Server-proces. Zie BULK INSERT voor een beschrijving van de syntaxis.

VOORBEELDEN VAN BULK INSERT

OPENROWSET(BULK...) Functie

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

Als u gegevens bulksgewijs wilt importeren, roept u OPENROWSET(BULK...) aan vanuit een SELECT...FROM component in de INSERT instructie.

De basissyntaxis voor het bulksgewijs importeren van gegevens is:

INSERT ... 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 CHECK-beperkingen)
  • IGNORE_TRIGGERS
  • KEEPDEFAULTS
  • KEEPIDENTITY

Zie Tabelhints (Transact-SQL) voor meer informatie.

Zie OPENROWSET BULK (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

Als u gegevens van een andere computer wilt gebruiken BULK INSERT of INSERT...SELECT * FROM OPENROWSET(BULK...) bulksgewijs wilt importeren, moet het gegevensbestand worden gedeeld tussen de twee computers. Als u een gedeeld gegevensbestand wilt opgeven, gebruikt u de naam van de UNC (Universal Naming Convention), die de algemene vorm heeft. \\Servername\Sharename\Path\Filename

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 bcp hulpprogramma omdat de client het bestand onafhankelijk van SQL Server leest.

Bulksgewijs importeren vanuit Azure Blob Storage

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

U kunt ook een CREATE 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 wordt niet ondersteund in SQL Server-versies vóór SQL Server 2025.

Notitie

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

BULK INSERT gebruiken

In het volgende voorbeeld ziet u hoe u de BULK INSERT opdracht 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 BULK INSERT opdracht 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

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

OPENROWSET gebruiken

In het volgende voorbeeld ziet u hoe u de OPENROWSET opdracht 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!
);

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