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:
-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