3

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?

2
  • I'm confused. Both tables have an identity key. Won't they both have the same data within the columns between each table?
    – jcolebrand
    Commented Feb 5, 2011 at 21:09
  • No, because in the SSIS package, I can't control the order of the data insertion. Because the destination table manages the identity key and auto increments, it assigns the wrong numbers.
    – rsteckly
    Commented Feb 7, 2011 at 20:47

2 Answers 2

4

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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.