Convert multiple int primary keys from different tables into Identity

Question

I have a dozen or so different databases with similar structure, with around 50 different tables each, and some of these tables used a sequential int [Id] as Primary Key and Identity.

At some point, these databases were migrated to a different remote infrastructure, namely from Azure to AWS, and somewhere in the process, the Identity property was lost, and as such, new automated inserts are not working as it fails to auto-increment the Id and generate a valid primary key.

I've tried multiple solutions, but am struggling to get any of them to work, as SQL-Server seems extremely finicky with letting you mess with or alter value of Identity columns in any way, and it's driving me insane.

I need to re-enable the Identity in multiple different tables, in multiple databases, but the solutions I've found so far are either extremely convoluted or impractical, for what seems to be a relatively simple problem.

tl;dr - How can I enable Identity for all my int primary keys in multiple different tables at the same time?

My approach so far:

CREATE PROC Fix_Identity @tableName varchar(50) 
AS
BEGIN
IF NOT EXISTS(SELECT * FROM sys.identity_columns WHERE OBJECT_NAME(object_id) = @tableName)
    BEGIN
    DECLARE @keyName varchar(100) = 'PK_dbo.' + @tableName;
    DECLARE @reName varchar(100) = @tableName + '.Id_new';

    EXEC ('Alter Table ' + @tableName + ' DROP CONSTRAINT ['+ @keyName +']');
    EXEC ('Alter Table ' + @tableName + ' ADD Id_new INT IDENTITY(1, 1) PRIMARY KEY');

    EXEC ('SET IDENTITY_INSERT [dbo].[' + @tableName + '] ON');
    EXEC ('UPDATE ' + @tableName + ' SET [Id_new] = [Id]');
    EXEC ('SET IDENTITY_INSERT [dbo].[' + @tableName + '] OFF');

    EXEC ('Alter Table ' + @tableName + ' DROP COLUMN Id');
    EXEC sp_rename @reName, 'Id', 'Column';
    END
END;

I tried creating this procedure, to be executed once per table, but i'm having problems with the UPDATE statement, which I require to guarantee that the new values Identity column will have the same value as the old Id column, but this approach currently doesn't work because:

Cannot update identity column 'Id_new'.

I have tried that, yes. It still does not solve my problem, because it requires me to remove the rows and add them again, while naming each column individually when doing the insert. This approach would be extremely time-consuming because i'm working with many tables and databases, unless I am missing something. — Marcos Braga, Jul 25 '18 at 20:08
It is quite easy to generate column lists for insert statements using e.g. `sys.columns`. — Aaron Bertrand, Jul 25 '18 at 20:10
The identity property is not as simple as flipping a switch. There is no easy button to just make your int columns have the identity property set to true. You are going to have to do this one table at a time. — Sean Lange, Jul 25 '18 at 20:10
I've added my current approach which is the closest I've come to a solution so far. Any insights on that? — Marcos Braga, Jul 25 '18 at 20:23
IIRC, `SET IDENTITY_INSERT..` does not affect UPDATE commands, only INSERTs. — RBarryYoung, Jul 25 '18 at 20:26
It is a mess to try and automate this, especially if you have Foreign Keys. — RBarryYoung, Jul 25 '18 at 20:32
This question has much useful information in the answers. https://stackoverflow.com/questions/1049210/adding-an-identity-to-an-existing-column — RBarryYoung, Jul 25 '18 at 20:50
This answer, in particular, describes almost exactly the same situation and what they had to do to fix it: https://stackoverflow.com/a/18521708/109122 — RBarryYoung, Jul 25 '18 at 20:51

score 1 · Answer 1 · answered Jul 25 '18 at 20:27

There are assumptions made in that script that you might want to look out for specifically with assuming the PK constraint name. You might want to double check that on all of your tables before. The rest of your script seemed to make sense to me except you will need to reseed the index after updating the data in the new column.

See if this helps:

select t.name AS [Table],c.Name AS [Non-Indent PK],i.name AS [PK Constraint]
from sys.columns c
inner join sys.tables t On c.object_id=t.object_id
inner join sys.indexes i ON i.object_id=c.object_id
    AND i.is_primary_key = 1
INNER JOIN sys.index_columns ic ON i.object_id=ic.object_id
    AND i.index_id = ic.index_id
    AND ic.column_id=c.column_id
WHERE c.Is_Identity=0

score 1 · Answer 2 · answered Jul 25 '18 at 21:27

Instead of adding an identity, create a sequence and default constraint

declare @table nvarchar(50) = N'dbo.T'  
declare @sql nvarchar(max) =  (N'select @maxID = max(Id) FROM ' + @table);
declare @maxID int   

exec sp_executesql @sql, N'@maxID int output', @maxID=@maxID OUTPUT;

set @sql = concat('create sequence ', @table, '_sequence start with ', @maxID + 1, ' increment by 1')
exec(@sql)

set @sql = concat('alter table ', @table, ' add default(next value for ', @table, '_sequence) for ID ')
exec(@sql)

Convert multiple int primary keys from different tables into Identity

2 Answers2