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?

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.

fskilnikAuthor Commented:
Hi, Brian!

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

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.