Keeping Identity Values When Importing Data in SQL Server 2005

---

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