patternMinor
Best way to sync a table between two sql server databases with an identity key
Viewed 0 times
identitybestdatabasessqlwithwaytwobetweenservertable
Problem
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?
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?
Solution
I would go this route:
SET IDENTITY_INSERT:
http://msdn.microsoft.com/en-us/library/ms188059.aspx
DBCC CHECKIDENT:
http://msdn.microsoft.com/en-us/library/ms176057.aspx
SET IDENTITY_INSERT ON
...
DBCC CHECKIDENT()
SET IDENTITY_INSERT OFFSET IDENTITY_INSERT:
http://msdn.microsoft.com/en-us/library/ms188059.aspx
DBCC CHECKIDENT:
http://msdn.microsoft.com/en-us/library/ms176057.aspx
Code Snippets
SET IDENTITY_INSERT <table> ON
...
DBCC CHECKIDENT(<table>)
SET IDENTITY_INSERT <table> OFFContext
StackExchange Database Administrators Q#1052, answer score: 4
Revisions (0)
No revisions yet.