Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
I'm confused. Both tables have an identity key. Won't they both have the same data within the columns between each table? –  jcolebrand Feb 5 '11 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 Feb 7 '11 at 20:47
add comment

2 Answers

up vote 4 down vote accepted

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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