Keep Identity Values When Bulk Importing Data (SQL Server)

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

• Keep Nulls or UseDefault Values During Bulk Import (SQL Server)

• Prepare Data for Bulk Export or Import (SQL Server)

To use a format file

• Create a Format File (SQL Server)

• Use a Format File to Bulk Import Data (SQL Server)

• Use a Format File to Map Table Columns to Data-File Fields (SQL Server)

• Use a Format File to Skip a Data Field (SQL Server)

• Use a Format File to Skip a Table Column (SQL Server)

To use data formats for bulk import or bulk export

• Import Native and Character Format Data from Earlier Versions of SQL Server

• Use Character Format to Import or Export Data (SQL Server)

• Use Native Format to Import or Export Data (SQL Server)

• Use Unicode Character Format to Import or Export Data (SQL Server)

• Use Unicode Native Format to Import or Export Data (SQL Server)

To specify data formats for compatibility when using bcp

1. Specify Field and Row Terminators (SQL Server)

2. Specify Prefix Length in Data Files by Using bcp (SQL Server)

3. Specify File Storage Type by Using bcp (SQL Server)

See Also

Reference

BACKUP (Transact-SQL)

bcp Utility

BULK INSERT (Transact-SQL)

OPENROWSET (Transact-SQL)

Table Hints (Transact-SQL)