SQL Server table identity field configuration

Hi there!

After using "sp_rename" in certain tables, copying another one (say <new>) "over" the original one (say <old>) , renaming again, etc, I created a "PK_Temp" Key in the <new>ly created one because I guess I did not created two identical tables, at first, as I should.

Well, everything looks ok, with the first column defined as UserID (PK, int, not null) by the way.

Question: why should I create an INSERT procedure WITH the UserID number as one of the VALUE fields, if (as an identity and key-related field) it should create the UserID automatically?

In other words, could you please tell what property should I check (probably alter) so that the identity field continues the behavior it had in the past, previously to the whole renaming and copying over stuff?

Thanks!
fskilnikAsked:
Who is Participating?
 
Brian CroweDatabase AdministratorCommented:
You cannot add the IDENTITY property to an existing column.  You will have to recreate either the column or the table.

You could add a new column with the IDENTITY property, enable IDENTITY_INSERT, and then copy the values over from the existing column, disable IDENTITY_INSERT, and delete the old column or you could create a new table with the correct schema and copy the data over.

Here is a link that might explain it in a little more detail.

http://sqlmag.com/t-sql/adding-identity-property-existing-column
0
 
fskilnikAuthor Commented:
Hi, Brian!

Got it. Thanks for the info and for the link.

Regards,
fskilnik
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.