How do I set IDENTITY on moved database tables?

Question

0.00/5 (No votes)

See more:

Hi all,

I have recently moved a Sql Server db from one server to another by scripting it to file and then running that script. Everything works but one thing - IDENTITY.

On the new server (SQL Server 2012 or 2014), the IDENTITY keyword (auto numbering) is ommitted from my tables. This means that new data can't be inserted for most tables!

I know that one solution would be to create a new column, say customerID2, with IDENTITY(1,1) set, and then remove the old one and rename the new one to customerID. However, such an approach would mess things up in tables where posts have been deleted. Let's say that the old table has ID's 1, 2, 5, 6. With IDENTITY on the new column, I would get 1, 2, 3, 4, and so, the foreign key values would be connected to the wrong primary key values.

How do I solve this problem? (If it's not possible to do it using IDENTITY I am up for other solutions as well. BTW, there are about 30 tables and 200 stored procedures working with these tables.)

Thanks, and best wishes for the new year.

Petter

Posted 2-Jan-16 22:04pm

petter2012

Add a Solution

Comments

Kornfeld Eliyahu Peter 3-Jan-16 4:11am

The first thing you should ask is 'Why no IDENTITY in my scripts?'...Any answers?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Duncan Edwards Jones · Accepted Answer · 2016-01-02T22:18:00

Solution 1

You can use the ALTER TABLE .. ALTER COLUMN (Transact-SQL)[^] but as these columns are probably used in foreign keys you will have to drop and recreate these keys either side of the alter table statement.

You might be able to make the change in SQL Server Manager and have it generate the requisite script.

Posted 2-Jan-16 22:18pm

Duncan Edwards Jones

Comments

petter2012 4-Jan-16 16:13pm

Hi Duncan,

Thanks for the reply. However I get an error when I try to set the IDENTITY keyword on an existing column in the new db instance. Just to confirm: Is this really doable? When I surf the net for solutions I only find that one cannot set IDENTITY on an exisiting column (ie with exisiting rows/IDs).

I have removed the FK's so that's not a problem.

Duncan Edwards Jones 4-Jan-16 16:50pm

You need to do a multiple-step process whereby: first you create a copy of the table structure but with the identity field, then you set IDENTITY INSERT ON for the table, then you copy the data from the source table and finally rename source and target tables so you are left with your new table having the identity field.

petter2012 4-Jan-16 16:19pm

Hi again,

According to posts in this thread: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/04d69ee6-d4f5-4f8f-a115-d89f7bcbc032/how-to-alter-column-to-identity11

it is possible to do it via the UI of SSMS, but not in code. Unfortunately i don't have the latest version installed so I will have to wait until I can download it. I just hope this is still the case - the posts are from 2007...

petter2012 4-Jan-16 20:34pm

Ok, thanks a lot. I'll try that!

Petter