We help IT Professionals succeed at work.
Get Started

Copy a primary key to new column and set Identity seed

Last Modified: 2012-05-09
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?
Watch Question
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2019
This problem has been solved!
Unlock 1 Answer and 6 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE