Sql-server – Best way to sync a table between two sql server databases with an identity key

sql-server-2008

I have an SSIS package that moves data from a table into another table in another database. The source table has an identity key.

I need to perform some operations on the data. The only way I can see to do this is to disable the identity key in the destination table and then use the alter table switch approach outlined here: https://stackoverflow.com/questions/1049210/adding-an-identity-to-an-existing-column-sql-server.

Is there an easier way to migrate data, preserve a key and then restart the identity column?

Best Answer

I would go this route:

SET IDENTITY_INSERT <table> ON
...
DBCC CHECKIDENT(<table>)
SET IDENTITY_INSERT <table> OFF

SET IDENTITY_INSERT:
http://msdn.microsoft.com/en-us/library/ms188059.aspx

DBCC CHECKIDENT:
http://msdn.microsoft.com/en-us/library/ms176057.aspx

Related Question