Friday, March 23, 2012

reset IDENTITY after table data import?

I have a remote DB I am wokring with at present. The DBA has provided me with a non owner LOGIN so I can't copy tables from the live to the staged DB as objects I can only copy tables and data.

The PKEY and IDENTITY COLUMNS get reset to just regular columns on each table. I can restore the PKEY constraint and have come across the DBCC CHECKIDENT to get the new ident value. I just can't figure out how to set a column to be an identity. The ALTER TABLE command isn't having any of it.

I am obviously missing the right bit on Books online

any suggestions?

many thanks

SteveThere isn't any way I know to add the IDENTITY property to an existing column. I'm pretty sure that it must be done at the time of the table creation.

My advice would be to script the table from the production DB, change the script to use IDENTITY, then copy the data from production to your working table.

-PatP

No comments:

Post a Comment