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

Question

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: http://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?

Answer 1

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

Answer 2

If you are using an OLE DB Destination this is handled for you via the "keep identity" check box.

See the Fast Load Options section in BOL.