I had to copy two tables from a database to another last night. After the tables were Imported successfully, the identity column and primary key constraint, however, were lost. To bring them back, there was no other way but to add a new column as identity and add constraint of primary key to that column; drop the old column and rename the new column to old column’s name. Here were what I did:

–this adds a new Identity column and automatically set not null and populate it with sequence data
alter table Category
add CategoryID2 int identity(1,1)
go
–drop the old CategoryId column
alter table Category
drop column CategoryID
go

–rename column CategoryID2 to CategoryID
sp_rename ‘Category.CategoryID2′,’CategoryID’,’Column’

–now add the primary key constraint
alter table Category
add constraint PK_CategoryId primary key clustered (CategoryId)
 

This completed the transition.