views:

1464

answers:

5

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!

+1  A: 

Guessing you didn't have much luck with your task....

In table design, you should be able to go into properties and under Identity Specification change (Is Identity) to Yes and assign the column primary key if it formerly had the primary key.

Tanner
Programmatically
tomaszs
if you do this, as descibed by @tanner, it will generate a script for you that you can look at and see how it is done
KM
+4  A: 

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
Diego
heh, what I wrote would of looked so much better if I had added code :P +1
SomeMiscGuy
Yes, and how to do this if I have 43 tables like this?
tomaszs
Tomaszs, I'm afraid you don't have much choice. If you have to "shift" IDs once, you can create a script that will do everything. If you have to do it often, then I'd start thinking of reviewing the database structure.
Diego
This guy needs a DBA - I hope he isn't in charge!
Tanner
@Tanner - why in third person? You're kidding right?
tomaszs
A: 

An Identity is a property that is set at the time the table is created or a new column is added in alter table statement. You can't alter the column and set it to identity and it is impossible to have two identity columns within the same table.

Depending on the size of the table, is it possible to simply create a new table? copy over the schema of the old one and then use SET IDENTITY_INSERT ON to populate the new identity column with what you want from the old one.

SomeMiscGuy
+1  A: 

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.

JoshBerke
A: 

From SqlServerCentral.com

Changing from Non-IDENTITY to IDENTITY and vice versa

Paul Rowland
I get a down vote for posting a link? Or for posting one to SQL Server Central? Anyway the article in question gives a couple ways of doing this similar to answers already given. Yes, you do have to register but its free and the site is worth it.
Paul Rowland