Keep Identity Values When Bulk Importing Data (SQL Server)

Article
06/13/2017

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).

Note

To create an automatically incrementing number that can be used in multiple tables or that can be called from applications without referencing any table, see Sequence Numbers.

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 AdventureWorks sample database under the dbo schema. To create this table. in SQL Server Management Studio Query Editor, execute:

USE AdventureWorks;  
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 AdventureWorks.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 AdventureWorks database. At the Windows command prompt, enter:

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

For more information about creating a format file, see Create a Format File (SQL Server).

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 AdventureWorks.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 AdventureWorks.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 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. 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 AdventureWorks.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 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

Related Tasks

To use a format file

To use data formats for bulk import or bulk export

To specify data formats for compatibility when using bcp

Share via