INF: How to Set Up Replication on Tables with an Identity Column

MORE INFORMATION

1. Manually synchronize the tables. To do this, create the table with the identity column at the subscriber and use BCP /E to bulk copy the data in.
2. Make sure the publication is defined to call a custom stored procedure for insert at the subscriber. For information on how to set up replication to use custom stored procedures, see the "Adding Stored Procedures for Insert, Update, and Delete" topic in the SQL Server Books Online.
3. Create the custom stored procedure for insert at the subscriber. This procedure must SET IDENTITY_INSERT ON, do the insert, and then set the option back to off. A sample procedure will look like the following:
   

         create proc cp_insert_table @c1 datatype, @c2 datatype
         /* The columns will be passed in order. */ 
         as
         begin
            set identity_insert <tablename> on
            insert tablename values (@c1, @c2)
            set identity_insert <tablename> off
         end
   
   						

4. Subscribe to this table with either the manual synchronization or no synchronization option. Then subsequent transactions can be replicated to the subscriber successfully.