Keeping Identity Values When Importing Data in SQL Server 2005

Published on Tuesday, October 5, 2010

If you have data tables which are set identity columns, you have to carefully when you migrate your database, or import / outport data. The point is: How to Keeping Identity Values When Importing Data in SQL Server 2005 ?

There are many solutions, the one is from Microsoft MSDN: Keeping Identity Values When Bulk Importing Data

Their information is using the following 3 ways:

1: bcp

-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

2: BULK INSERT

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;

**3: INSERT ... SELECT * FROM OPENROWSET(BULK…) **

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

For detail info please read their page