Importieren von Daten nach SQL Server mithilfe von BULK INSERT oder OPENROWSET(BULK...)

Gilt für: SQL Server (alle unterstützten Versionen) Azure SQL-Datenbank Azure SQL Managed Instance

In diesem Artikel erhalten Sie einen Überblick über die Verwendung der Transact-SQL-Anweisungen BULK INSERT und INSERT...SELECT * FROM OPENROWSET(BULK...), mit denen ein Massenimport von Daten aus einer Datendatei in eine SQL Server-Tabelle oder eine Tabelle in Azure SQL-Datenbank möglich ist. In diesem Artikel werden zudem Sicherheitsaspekte beim Verwenden von BULK INSERT und OPENROWSET(BULK…) sowie das Verwenden dieser Methoden für den Massenimport aus einer Remotedatenquelle beschrieben.

Hinweis

Für die Verwendung von BULK INSERT oder OPENROWSET(BULK…) ist es wichtig, nachvollziehen zu können, wie Identitätswechsel von SQL Server-Versionen verarbeitet werden. Weitere Informationen finden Sie unter "Sicherheitsüberlegungen" weiter unten in diesem Thema.

BULK INSERT-Anweisung

BULK INSERT lädt Daten aus einer Datendatei in eine Tabelle. Die Funktionalität ähnelt derjenigen der Option in des bcp -Befehls. Die Datendatei wird jedoch vom SQL Server -Prozess gelesen. Eine Beschreibung der BULK INSERT-Syntax finden Sie unter BULK INSERT (Transact-SQL).

BULK INSERT-Beispiele

OPENROWSET(BULK…) Funktion

Auf den OPENROWSET-Massenrowsetanbieter wird durch Aufrufen der OPENROWSET-Funktion und Angeben der BULK-Option zugegriffen. Mithilfe der OPENROWSET(BULK…)-Funktion können Sie auf Remotedaten zugreifen, indem Sie über einen OLE DB-Anbieter eine Verbindung mit einer Remotedatenquelle (z. B. einer Datendatei) herstellen.

Für den Massenimport von Daten wird OPENROWSET(BULK…) aus der SELECT…FROM-Klausel einer INSERT-Anweisung aufgerufen. Die grundlegende Syntax für den Massenimport von Daten lautet:

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

Wenn OPENROWSET(BULK...) in einer INSERT-Anweisung verwendet wird, werden damit auch Tabellenhinweise unterstützt. Zusätzlich zu den regulären Tabellenhinweisen wie TABLOCK kann die BULK-Klausel die folgenden spezialisierten Tabellenhinweise akzeptieren: IGNORE_CONSTRAINTS (ignoriert nur die CHECK-Einschränkungen), IGNORE_TRIGGERS, KEEPDEFAULTS und KEEPIDENTITY. Weitere Informationen finden Sie unter Tabellenhinweise (Transact-SQL).

Informationen zu den zusätzlichen Verwendungsmöglichkeiten der Option BULK finden Sie unter OPENROWSET (Transact-SQL).

INSERT...SELECT * FROM OPENROWSET(BULK...)-Anweisungen – Beispiele:

Sicherheitshinweise

Wenn ein Benutzer einen SQL Server -Anmeldenamen verwendet, wird das Sicherheitsprofil des SQL Server -Prozesskontos verwendet. Ein Anmeldename, für den die SQL Server-Authentifizierung verwendet wird, kann nicht außerhalb der Datenbank-Engine authentifiziert werden. Wenn ein BULK INSERT-Befehl durch einen Anmeldenamen initiiert wird, der die SQL Server-Authentifizierung verwendet, wird die Datenverbindung folglich mithilfe des Sicherheitskontexts des SQL Server-Prozesskontos (dem vom SQL Server-Datenbank-Engine-Dienst verwendeten Konto) hergestellt.

Um die Quelldaten lesen zu können, müssen Sie dem von der SQL Server-Datenbank-Engine verwendeten Konto Zugriff auf die Quelldaten gewähren. Wenn sich hingegen ein SQL Server -Benutzer mithilfe der Windows-Authentifizierung anmeldet, können von diesem Benutzer nur die Dateien gelesen werden, auf die über das Benutzerkonto zugegriffen werden kann. Das Sicherheitsprofil des SQL Server -Prozesses wird dabei nicht berücksichtigt.

Angenommen, ein Benutzer hat sich mithilfe der Windows-Authentifizierung an einer Instanz von SQL Server angemeldet. Damit der Benutzer zum Importieren von Daten aus einer Datendatei in eine SQL Server -Tabelle BULK INSERT oder OPENROWSET verwenden kann, muss das Konto über Lesezugriff für die Datendatei verfügen. Durch den Zugriff auf die Datendatei kann der Benutzer Daten aus der Datei in eine Tabelle importieren, selbst wenn für den SQL Server -Prozess keine Berechtigung zum Zugreifen auf die Datei verfügbar ist. Der Benutzer muss dem SQL Server -Prozess keine Dateizugriffsberechtigung erteilen.

SQL Server und Microsoft Windows können so konfiguriert werden, dass von einer SQL Server -Instanz eine Verbindung mit einer anderen SQL Server -Instanz hergestellt wird, indem die Anmeldeinformationen eines authentifizierten Windows-Benutzers weitergeleitet werden. Diese Anordnung wird als Identitätswechsel oder Delegierungbezeichnet. Es ist wichtig zu wissen, wie in der SQL Server -Version die Sicherheit für den Benutzeridentitätswechsel verarbeitet wird, wenn Sie BULK INSERT oder OPENROWSET verwenden. Durch einen Benutzeridentitätswechsel kann sich die Datendatei auf einem anderen Computer befinden als der SQL Server -Prozess oder der Benutzer selbst. Wenn beispielsweise ein Benutzer auf Computer_A Zugriff auf eine Datendatei hat, die sich auf Computer_Bbefindet, und die Delegierung der Anmeldeinformationen entsprechend festgelegt ist, kann der Benutzer eine Verbindung mit einer SQL Server -Instanz herstellen, die auf Computer_Causgeführt wird, auf die Datendatei auf Computer_Bzugreifen und einen Massenimport von Daten aus der Datei in eine Tabelle auf Computer_Causführen.

Massenimport in SQL Server aus einer Remotedatendatei

Die Datendatei muss zwischen zwei Computern freigegeben sein, um mithilfe von BULK INSERT oder INSERT...SELECT * FROM OPENROWSET(BULK...) den Massenimport von Daten von einem Computer zum anderen auszuführen. Verwenden Sie zum Angeben einer freigegebenen Datendatei den UNC-Namen (UNC = Universal Naming Convention), der allgemein dem folgenden Format folgt: \\Servername\Freigabename\Pfad\Dateiname. Zudem muss das Konto, mit dem auf die Datendatei zugegriffen wird, über die Berechtigungen verfügen, die zum Lesen der Datei auf dem Remotedatenträger erforderlich sind.

Beispielsweise wird mithilfe der folgenden BULK INSERT -Anweisung ein Massenimport von Daten aus der Datendatei SalesOrderDetail in die AdventureWorks -Tabelle der newdata.txt-Datenbank ausgeführt. Diese Datendatei befindet sich im freigegebenen Ordner \dailyorders auf dem salesforce -Netzwerkfreigabeverzeichnis des computer2-Systems.

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

Hinweis

Diese Einschränkung gilt nicht für das Hilfsprogramm bcp, da die Datei vom Client unabhängig von SQL Server gelesen wird.

Massenimport aus Azure Blob Storage

Wenn Sie Daten aus Azure Blob Storage importieren, die nicht öffentlich sind (anonymer Zugriff), erstellen Sie eine DATABASE SCOPED CREDENTIAL-Anweisung, die auf einem SAS-Schlüssel basiert, der mit einer MASTER KEY-Anweisung verschlüsselt ist. Erstellen Sie anschließend eine externe Datenbankquelle für die Verwendung in Ihrem BULK INSERT-Befehl.

Hinweis

Verwenden Sie keine explizite Transaktion, diese führt zu einem 4861-Fehler.

Verwenden von BULK INSERT

Das folgende Beispiel zeigt, wie Daten mithilfe des BULK INSERT-Befehls aus einer CSV-Datei in einen Speicherort von Azure Blob Storage geladen werden, für den Sie einen SAS-Schlüssel erstellt haben. Der Speicherort von Azure Blob Storage wird als externe Datenquelle konfiguriert. Hierfür sind datenbankweit gültige Anmeldeinformationen mit einer Shared Access Signature (SAS) erforderlich, die mit einem Hauptschlüssel in der Benutzerdatenbank verschlüsselt ist.

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

Wichtig

Das Lesen aus Windows-Dateien wird von Azure SQL-Datenbank nicht unterstützt.

Verwenden von OPENROWSET

Das folgende Beispiel zeigt, wie Daten mithilfe des OPENROWSET-Befehls aus einer CSV-Datei in einen Speicherort von Azure Blob Storage geladen werden, für den Sie einen SAS-Schlüssel erstellt haben. Der Speicherort von Azure Blob Storage wird als externe Datenquelle konfiguriert. Hierfür sind datenbankweit gültige Anmeldeinformationen mit einer Shared Access Signature (SAS) erforderlich, die mit einem Hauptschlüssel in der Benutzerdatenbank verschlüsselt ist.

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

Wichtig

Das Lesen aus Windows-Dateien wird von Azure SQL-Datenbank nicht unterstützt.

Weitere Informationen