Freigeben über

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

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed Instance

Dieser Artikel enthält eine Übersicht über die Verwendung der Transact-SQL-Anweisung BULK INSERT und der INSERT...SELECT * FROM OPENROWSET(BULK...) Anweisung zum Massenimport von Daten aus einer Datendatei in eine SQL Server- oder Azure SQL-Datenbanktabelle.

In diesem Artikel werden auch Sicherheitsüberlegungen für die Verwendung BULK INSERT und OPENROWSET(BULK...)verwendung dieser Methoden zum Massenimport aus einer Remotedatenquelle beschrieben.

BULK INSERT-Anweisung

BULK INSERT Lädt Daten aus einer Datendatei in eine Tabelle. Diese Funktionalität ähnelt der Funktion, die von der in Option des bcp Befehls bereitgestellt wird. 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 Massen-Rowset-Anbieter wird zugegriffen, indem die OPENROWSET Funktion aufgerufen und die BULK Option angegeben wird. Mit der OPENROWSET(BULK...) Funktion können Sie auf Remotedaten zugreifen, indem Sie eine Verbindung mit einer Remotedatenquelle herstellen, z. B. eine Datendatei, über einen Datenanbieter.

Rufen Sie zum Massenimport von Daten aus einer SELECT...FROM Klausel in einer INSERT Anweisung aufOPENROWSET(BULK...).

Die grundlegende Syntax für den Massenimport von Daten lautet:

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

Wenn sie in einer INSERT Anweisung verwendet wird, OPENROWSET(BULK...) werden Tabellenhinweise unterstützt. Zusätzlich zu den regulären Tabellenhinweisen, z TABLOCK. B., kann die BULK Klausel die folgenden speziellen Tabellenhinweise akzeptieren:

  • IGNORE_CONSTRAINTS (ignoriert nur die CHECK-Einschränkungen)
  • IGNORE_TRIGGERS
  • KEEPDEFAULTS
  • KEEPIDENTITY

Weitere Informationen finden Sie unter Tabellenhinweise (Transact-SQL).

Informationen zu zusätzlichen Verwendungsmöglichkeiten der BULK-Option finden Sie unter OPENROWSET BULK (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. Eine Anmeldung mit SQL Server-Authentifizierung kann nicht außerhalb der Datenbank-Engine authentifiziert werden. Daher wird beim Initiieren eines BULK INSERT Befehls mithilfe der SQL Server-Authentifizierung die Verbindung mit den Daten mithilfe des Sicherheitskontexts des SQL Server-Prozesskontos (das vom SQL Server-Datenbankmoduldienst verwendete 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 SQL Server-Instanz 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_B befindet, und die Delegierung der Anmeldeinformationen entsprechend festgelegt ist, kann der Benutzer eine Verbindung mit einer SQL Server-Instanz herstellen, die auf Computer_C ausgeführt wird, auf die Datendatei auf Computer_B zugreifen und einen Massenimport von Daten aus der Datei in eine Tabelle auf Computer_C ausführen.

Massenimport in SQL Server aus einer Remotedatendatei

Um Daten von einem anderen Computer zu verwenden BULK INSERT oder INSERT...SELECT * FROM OPENROWSET(BULK...) zu importieren, muss die Datendatei zwischen den beiden Computern gemeinsam verwendet werden. Verwenden Sie zum Angeben einer freigegebenen Datendatei den Namen der universellen Benennungskonvention (UNC), der die allgemeine Form verwendet. \\Servername\Sharename\Path\Filename

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 AdventureWorks2022.Sales.SalesOrderDetail
   FROM '\\computer2\salesforce\dailyorders\neworders.txt';

Hinweis

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

Massenimport aus Azure Blob Storage

Erstellen Sie beim Importieren aus Azure Blob Storage und die Daten nicht öffentlich (anonymer Zugriff), erstellen Sie eine CREATE DATABASE SCOPED CREDENTIAL basierend auf einem SAS-Schlüssel, der mit einem CREATE MASTER KEY verschlüsselt ist, und erstellen Sie dann eine externe Datenbankquelle für die Verwendung in Ihrem BULK INSERT Befehl.

Erstellen Sie alternativ eine CREATE DATABASE SCOPED-ANMELDEINFORMATIONEN basierend auf MANAGED IDENTITY der Autorisierung von Anforderungen für den Datenzugriff in nicht öffentlichen Speicherkonten. Bei Verwendung von MANAGED IDENTITY muss Azure Storage Berechtigungen für die verwaltete Identität der Instanz erteilen, indem die integrierte Rolle Mitwirkender an Storage-Blobdaten hinzugefügt wird, die rollenbasierte Zugriffssteuerung in Azure (Role-Based Access Control, RBAC) mit Lese-/Schreibzugriff auf die verwaltete Identität für die erforderlichen Azure Blob Storage-Container ermöglicht. Azure SQL Managed Instance verfügt über eine systemseitig zugewiesene verwaltete Identität und kann auch mindestens eine benutzerseitig zugewiesene verwaltete Identität umfassen. Für die Autorisierung der Anforderungen können Sie entweder systemseitig oder benutzerseitig zugewiesene verwaltete Identitäten verwenden. Für die Autorisierung wird die default-Identität der verwalteten Instanz verwendet (dies ist die primäre benutzerseitig zugewiesene verwaltete Identität oder systemseitig zugewiesene verwaltete Identität, wenn keine benutzerseitig zugewiesene verwaltete Identität angegeben ist).

Wichtig

Verwaltete Identität wird in SQL Server-Versionen vor SQL Server 2025 nicht unterstützt.

Hinweis

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

MASSENEINFÜGUNG verwenden

Das folgende Beispiel zeigt, wie Sie mit dem BULK INSERT Befehl Daten aus einer CSV-Datei an einem Azure Blob-Speicherort laden, auf dem 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');

Das folgende Beispiel zeigt, wie Sie mit dem BULK INSERT Befehl Daten aus einer CSV-Datei in einem Azure Blob-Speicherort mithilfe der verwalteten Identität laden. Der Speicherort von Azure Blob Storage wird als externe Datenquelle konfiguriert.

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

Wichtig

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

Verwenden von OPENROWSET

Das folgende Beispiel zeigt, wie Sie mit dem OPENROWSET Befehl Daten aus einer CSV-Datei an einem Azure Blob-Speicherort laden, auf dem 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.

Zugehöriger Inhalt