How to change programmatically non-identity column to identity one?

Question

I have a table with column ID that is identity one. Next I create new non-identity column new_ID and update it with values from ID column + 1. Like this:

new_ID = ID + 1

Next I drop ID column and rename new_ID to name 'ID'.

And how to set Identity on this new column 'ID'?

I would like to do this programmatically!

Answer 1

As far as I know, you have to create a temporary table with the ID field created as IDENTITY, then copy all the data from the original table. Finally, you drop the original table and rename the temporary one. This is an example with a table (named TestTable) that contains only one field, called ID (integer, non IDENTITY):

BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_TestTable
    (
    ID int NOT NULL IDENTITY (1, 1)
    )  ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_TestTable ON
GO
IF EXISTS(SELECT * FROM dbo.TestTable)
     EXEC('INSERT INTO dbo.Tmp_TestTable (ID)
        SELECT ID FROM dbo.TestTable WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_TestTable OFF
GO
DROP TABLE dbo.TestTable
GO
EXECUTE sp_rename N'dbo.Tmp_TestTable', N'TestTable', 'OBJECT' 
GO
COMMIT

Answer 2

Looks like SQL Mobile supports altering a columns identity but on SQL Server 2005 didn't like the example from BOL.

So your options are to create a new temporary table with the identity column, then turn Identity Insert on:

Create Table Tmp_MyTable ( Id int identity....)

SET IDENTITY_INSERT dbo.Tmp_Category ON

INSERT Into Tmp_MyTable (...)
Select From MyTable ....

Drop Table myTable

EXECUTE sp_rename N'dbo.Tmp_MyTable', N'MyTable', 'OBJECT' 

Additionally you can try and add the column as an identity column in the first place and then turn identity insert on. Then drop the original column. But I am not sure if this will work.