views:

100

answers:

3

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
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
A: 

If you're talking MySQL, a simple export/drop/create process should work just fine.

Webnet
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