Beibehalten von Identitätswerten beim Massenimport von Daten
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).
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 die Beispiele zum Massenimport muss in der AdventureWorks2008R2-Beispieldatenbank unter dem dbo-Schema eine Tabelle namens myTestKeepNulls erstellt werden. Führen Sie zum Erstellen dieser Tabelle im Abfrage-Editor von SQL Server Management Studio folgenden Befehl aus:
USE AdventureWorks2008R2;
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 AdventureWorks2008R2.HumanResources.Department out myDepartment-n.Dat -n -T
Beispielformatdatei
In diesem 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 aus der HumanResources.Department-Tabelle der AdventureWorks2008R2-Datenbank generiert. Geben Sie an der Windows-Eingabeaufforderung Folgendes ein:
bcp AdventureWorks2008R2.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.
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 über eine vertrauenswürdige Verbindung die Verbindung mit SQL Server herstellt. |
Geben Sie an der Windows-Eingabeaufforderung Folgendes ein:
bcp AdventureWorks2008R2.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 AdventureWorks2008R2.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 AdventureWorks2008R2;
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 AdventureWorks2008R2.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 Abfrage-Editor von SQL Server Management Studio folgenden Befehl aus:
USE AdventureWorks2008R2;
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