How can we transfer primary key (identity column) while replicating a database.
A:
You'll need to set the tables IDENTITY_INSERT to on and after everything is done turn it back off again.
SET IDENTITY_INSERT test ON
-- Inserts
SET IDENTITY_INSERT test OFF
But for more info and exceptions check this great article.
http://www.simple-talk.com/sql/database-administration/the-identity-crisis-in-replication/
JeremySpouken
2010-03-10 15:31:13
This will not work if you have a permanent replication set up between two server, or if you are replicating two or more tables. Only one table can have IDENTITY_INSERT turned ON at the time.
Frode N. Rosand
2010-03-10 15:33:12
A:
If you're talking MySQL, a simple export/drop/create process should work just fine.
Webnet
2010-03-10 15:31:26
A:
Remove the "identity" definition on your target table and you can copy directly from the source table. This will only work if the replication is one-way.
Frode N. Rosand
2010-03-10 15:31:34