IDENTITY COLUMN

[3615]

how to change IDENTIY column to IDENTITY NOT FOR REPLICATION using script.
(I need to keep same column values)
Thanks.

 

[kavin]

When the IDENTITY property is used with CREATE TABLE, Microsoft® SQL Server™ uses the NOT FOR REPLICATION option of CREATE TABLE to override the automatic incrementing of an identity column. Usually, SQL Server assigns each new row inserted in a table a value that is some increment greater than the previous highest value. However, if the new rows are replicated from another data source, the identity values must remain exactly as they were at the data source.

 

[lalgudi]

Hi,

You can use bcp with an -E option to prevent the identity column from getting corrupted. You have first bcp out the data from the source and then bcp in with an -E option. I have tried it and it works very well.

lalgudi

 

[3615]

I need to use a SCRIPT SQL to change IDENTIIY column to IDENTITY NOT FOR REPLICATION
on multiples Tables.
The original column values must be the same after changing property column.

This change can be done manually using SQL Entreprise Manager but i need a SCRIPT to change 100 tables on 10 differents databases...