Click here to Skip to main content
15,559,931 members
Please Sign up or sign in to vote.
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
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

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.
 
Share this answer
 
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

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900