We help IT Professionals succeed at work.

Copy a primary key to new column and set Identity seed

I have a remote sql server database provided by a host that I can connect to using SQL Server Management Studio.

I exported the tables from my local database and all seemed fine until I saw that some tables had lost the id fields' primary key setting on the column and also the identity seed. This means that I can't create new rows using my website admin system.

I've tried right clicking and changing the column properties through mangement studio, but no luck, tried doing via an SQL query also no luck. There seem to be persmissions / constraint issues that are preventing me from doing this.

How can I make my if fields primary keys and have identity(1,1) again? Is there a way to copy the contents of the column to a new column?

I tried creating a new column that is a primary key and has identity(1,1) but I can't update the values in this column from the old id field as its not allowed.

What are my options?
Comment
Watch Question

SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2019
Commented:
>> There seem to be persmissions / constraint issues that are preventing me from doing this.

It was Constraints issue you are facing..
You can't set the identity property unless otherwise you drop primary key and Foreign key constraint created on that column..

If you have lesser data present in your database, then use Database Publishing Wizard to script your entire database structure along with data present in it into a single script file.
Once generated, just include IDENTITY(1,1) in the required columns and re-run that script file again dropping the existing database which should do..

And you can get Database Publishing wizard from here:

http://www.microsoft.com/downloads/details.aspx?familyid=56e5b1c5-bf17-42e0-a410-371a838e570a&displaylang=en
Top Expert 2007

Author

Commented:
I've got SQL Server 2008, Database publishing wizard seems to need SQL Server 2005. Is there a 2008 equivalent?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2019

Commented:
If you are using SQL Server 2008, then no issues, you can use the built-in Generate Scripts wizard, but make sure in the Choose Scripts Options page, you have Script Data option set to True so that you can script both DDLs and data..

And follow the rest of the steps mentioned earlier..
Top Expert 2007

Author

Commented:
Instead of the data publishing or generate scripts I ended up using an 'export' option in the hosts web admin control panel and modified the create table commands with

NOT NULL PRIMARY KEY IDENTITY (1,1)

I then used the following to insert my old data into the new table copies.

SET identity_insert [mytable] on

-- INSERT COMMANDS GO HERE, INSERT COMMANDS CAN HAVE THE ID / PRIMARY KEY / IDENTITY FIELD with custom numbers / old numbers

SET identity_insert [mytable] off
Top Expert 2007

Author

Commented:
Thanks mate!
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2019

Commented:
Welcome..