Beibehalten von Identitätswerten beim Massenimport von Daten (SQL Server)

Artikel
06/25/2013

Datendateien, die Identitätswerte enthalten, können per Massenimport in eine Instanz von Microsoft SQL Server übertragen werden. Standardmäßig werden die Werte für die Identitätsspalte in der importierten Datendatei ignoriert, und von SQL Server werden automatisch eindeutige Werte zugewiesen. Die eindeutigen Werte basieren auf dem Ausgangswert und den inkrementellen Werten, die bei der Tabellenerstellung angegeben werden.

Wenn die Datendatei keine Werte für die Bezeichnerspalte in der Tabelle enthält, sollten Sie eine Formatdatei verwenden, um anzugeben, dass die Bezeichnerspalte beim Importieren von Daten ausgelassen werden soll. SQL Server weist der Spalte automatisch eindeutige Werte zu.

Um zu verhindern, dass SQL Server beim Massenimport von Datenzeilen in eine Tabelle Identitätswerte zuweist, verwenden Sie den entsprechenden Qualifizierer für den Befehl zur Identitätsbeibehaltung (keepidentity). Wenn Sie einen Qualifizierer zur Identitätsbeibehaltung angeben, verwendet SQL Server die Identitätswerte in der Datendatei. Nachfolgend sind diese Qualifizierer aufgeführt:

Befehl	Qualifizierer zur Identitätsbeibehaltung	Qualifizierertyp
bcp	-E	Schalter
BULK INSERT	KEEPIDENTITY	Argument
INSERT ... SELECT * FROM OPENROWSET(BULK...)	KEEPIDENTITY	Tabellenhinweis

Weitere Informationen finden Sie unter bcp (Hilfsprogramm), BULK INSERT (Transact-SQL), OPENROWSET (Transact-SQL), INSERT (Transact-SQL), SELECT (Transact-SQL) und Tabellenhinweise (Transact-SQL).

Hinweis
Weitere Informationen zu einer automatisch inkrementierten Nummer, die in mehreren Tabellen verwendet oder aus Anwendungen aufgerufen werden kann, ohne dass auf eine Tabelle verwiesen wird, finden Sie unter Sequenznummern.

Beispiele

In den in diesem Thema enthaltenen Beispielen wird der Massenimport mithilfe von INSERT ... SELECT * FROM OPENROWSET(BULK...) ausgeführt, und die Standardwerte werden beibehalten.

Beispieltabelle

Für den Massenimport muss eine Tabelle namens myTestKeepNulls erstellt werden, und zwar in der AdventureWorks-Beispieldatenbank unter dem dbo-Schema. Führen Sie zum Erstellen dieser Tabelle im SQL Server Management Studio-Abfrage-Editor Folgendes aus:

USE AdventureWorks;
GO
SELECT * INTO HumanResources.myDepartment 
   FROM HumanResources.Department
      WHERE 1=0;
GO
SELECT * FROM HumanResources.myDepartment;

Für die Department-Tabelle, auf der myDepartment basiert, ist IDENTITY_INSERT auf OFF festgelegt. Wenn Sie also Daten in eine Identitätsspalte importieren möchten, müssen Sie KEEPIDENTITY oder -E angeben.

Beispieldatendatei

Die in den Beispielen zum Massenimport verwendete Datendatei enthält massenkopierte Daten, die im systemeigenen Format aus der HumanResources.Department-Tabelle exportiert wurden. Geben Sie zur Erstellung der Datendatei an der Microsoft Windows-Eingabeaufforderung Folgendes ein:

bcp AdventureWorks.HumanResources.Department out myDepartment-n.Dat -n -T

Beispielformatdatei

In diesen Beispielen für den Massenimport wird eine Datei im XML-Format verwendet (myDepartment-f-x-n.Xml), von der wiederum systemeigene Datenformate verwendet werden. In diesem Beispiel wird diese Formatdatei mit bcp mithilfe der HumanResources.Department-Tabelle der AdventureWorks-Datenbank generiert. Geben Sie an der Windows-Eingabeaufforderung Folgendes ein:

bcp AdventureWorks.HumanResources.Department format nul -n -x -f myDepartment-f-n-x.Xml -T

Weitere Informationen zum Erstellen einer Formatdatei finden Sie unter Erstellen einer Formatdatei (SQL Server).

A.Mit "bcp" und unter Beibehaltung der Identitätswerte

Anhand des nachfolgenden Beispiels wird veranschaulicht, wie Identitätswerte beibehalten werden können, wenn bcp für den Massenimport von Daten verwendet wird. Der Befehl bcp verwendet die Formatdatei myDepartment-f-n-x.Xml und enthält folgende Schalter:

Qualifizierer	Beschreibung
-E	Gibt an, dass Identitätswerte in der Datendatei für die Identitätsspalte verwendet werden sollen.
-T	Gibt an, dass das Hilfsprogramm "bcp" eine vertrauenswürdige Verbindung mit SQL Server herstellt.

Geben Sie an der Windows-Eingabeaufforderung Folgendes ein.

bcp AdventureWorks.HumanResources.myDepartment in C:\myDepartment-n.Dat -f C:\myDepartment-f-n-x.Xml -E -T

B.Mit BULK INSERT und unter Beibehaltung der Identitätswerte

Im nachfolgenden Beispiel wird BULK INSERT für den Massenimport von Daten aus der myDepartment-c.Dat-Datei in die AdventureWorks.HumanResources.myDepartment-Tabelle verwendet. Die Anweisung verwendet die myDepartment-f-n-x.Xml-Formatdatei und nimmt die Option KEEPIDENTITY auf, um sicherzustellen, dass sämtliche Identitätswerte in der Datendatei beibehalten werden.

Führen Sie im Abfrage-Editor von SQL Server Management Studio folgenden Befehl aus:

USE AdventureWorks;
GO
DELETE HumanResources.myDepartment;
GO
BULK INSERT HumanResources.myDepartment
   FROM 'C:\myDepartment-n.Dat'
   WITH (
      KEEPIDENTITY,
      FORMATFILE='C:\myDepartment-f-n-x.Xml'
   );
GO
SELECT * FROM HumanResources.myDepartment;

C.Mit OPENROWSET und unter Beibehaltung der Identitätswerte

Im nachfolgenden Beispiel wird der OPENROWSET-Massen-Rowset-Anbieter zum Massenimport von Daten aus der myDepartment-c.Dat-Datei in die AdventureWorks.HumanResources.myDepartment-Tabelle verwendet. Die Anweisung verwendet die myDepartment-f-n-x.Xml-Formatdatei und nimmt den KEEPIDENTITY-Hinweis auf, um sicherzustellen, dass sämtliche Identitätswerte in der Datendatei beibehalten werden.

Führen Sie im SQL Server Management Studio-Abfrage-Editor Folgendes aus:

USE AdventureWorks;
GO
DELETE HumanResources.myDepartment;
GO

INSERT INTO HumanResources.myDepartment
   with (KEEPIDENTITY)
   (DepartmentID, Name, GroupName, ModifiedDate)
   SELECT *
      FROM  OPENROWSET(BULK 'C:\myDepartment-n.Dat',
      FORMATFILE='C:\myDepartment-f-n-x.Xml') as t1;
GO