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