Keeping Identity Values When Bulk Importing Data
Data files that contain identity values can be bulk imported into an instance of Microsoft SQL Server. By default, the values for the identity column in the data file that is imported are ignored and SQL Server assigns unique values automatically. The unique values are based on the seed and increment values that are specified during table creation.
If the data file does not contain values for the identifier column in the table, use a format file to specify that the identifier column in the table should be skipped when importing data. SQL Server assigns unique values for the column automatically.
To prevent SQL Server from assigning identity values while bulk importing data rows into a table, use the appropriate keep-identity command qualifier. When you specify a keep-identity qualifier, SQL Server uses the identity values in the data file. These qualifiers are as follows:
Command |
Keep-identity qualifier |
Qualifier type |
|---|---|---|
bcp |
-E |
Switch |
BULK INSERT |
KEEPIDENTITY |
Argument |
INSERT ... SELECT * FROM OPENROWSET(BULK...) |
KEEPIDENTITY |
Table hint |
For more information, see bcp Utility, BULK INSERT (Transact-SQL), OPENROWSET (Transact-SQL), INSERT (Transact-SQL), SELECT (Transact-SQL), and Table Hints (Transact-SQL).
Examples
The examples in this topic bulk import data using INSERT ... SELECT * FROM OPENROWSET(BULK...) and keeping default values.
Sample Table
The bulk-import examples require that a table named myTestKeepNulls table be created in the AdventureWorks2008R2 sample database under the dbo schema. To create this table. in SQL Server Management Studio Query Editor, execute:
USE AdventureWorks2008R2;
GO
SELECT * INTO HumanResources.myDepartment
FROM HumanResources.Department
WHERE 1=0;
GO
SELECT * FROM HumanResources.myDepartment;
The Department table on which myDepartment is based has IDENTITY_INSERT is set to OFF. Therefore, to import data into an identity column you must specify KEEPIDENTITY or -E.
Sample Data File
The data file used in the bulk-import examples contains data bulk exported from the HumanResources.Department table in native format. To create the data file, at the Microsoft Windows command prompt, enter:
bcp AdventureWorks2008R2.HumanResources.Department out myDepartment-n.Dat -n -T
Sample Format File
This bulk-import examples use an XML format file, myDepartment-f-x-n.Xml, which uses native data formats. This example uses bcp to create to generate this format file from the HumanResources.Department table of the AdventureWorks2008R2 database. At the Windows command prompt, enter:
bcp AdventureWorks2008R2.HumanResources.Department format nul -n -x -f myDepartment-f-n-x.Xml -T
For more information about creating a format file, see Creating a Format File.
A. Using bcp and Keeping Identity Values
The following example demonstrates how to keep identity values when using bcp to bulk import data. The bcp command uses the format file, myDepartment-f-n-x.Xml, and contains the following switches:
Qualifiers |
Description |
|---|---|
-E |
Specifies that identity value or values in the data file are to be used for the identity column. |
-T |
Specifies that the bcp utility connects to SQL Server with a trusted connection. |
At the Windows command prompt, enter.
bcp AdventureWorks2008R2.HumanResources.myDepartment in C:\myDepartment-n.Dat -f C:\myDepartment-f-n-x.Xml -E -T
B. Using BULK INSERT and Keeping Identity Values
The following example uses BULK INSERT to bulk import data from the myDepartment-c.Dat file into the AdventureWorks2008R2.HumanResources.myDepartment table. The statement uses the myDepartment-f-n-x.Xml format file and includes the KEEPIDENTITY option to ensure that any identity values in the data file are retained.
In the SQL Server Management Studio Query Editor, execute:
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. Using OPENROWSET and Keeping Identity Values
The following example uses the OPENROWSET bulk rowset provider to bulk import data from the myDepartment-c.Dat file into the AdventureWorks2008R2.HumanResources.myDepartment table. The statement uses the myDepartment-f-n-x.Xml format file and includes the KEEPIDENTITY hint to ensure that any identity values in the data file are retained.
In the SQL Server Management Studio Query Editor, execute:
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