Change Column Type to Identity 1

[Bonediggler1]

Hi--

I would like to change the data type of a column to IDENTITY. I am using the following code but get the 'Incorrect syntax near the keyword 'IDENTITY' error:

ALTER TABLE DBO.AUDIT
ALTER COLUMN AUDIT_ID SMALLINT IDENTITY (1,1)


Thanks for the help!!

 

[SQLSister]

Can't do that. You have to create a new table with the identity column in place, then move the data using identity_insert set to ON, then delete the old table and rename the new one.

"NOTHING is more important in a database than integrity." ESquared

 

[TysonLPrice]

I think you can do it from Enterprise Manager SQL 2005 in the design mode. I just did that. The table had nine rows. I started the new identity column added seed at one and it increments all the rows. Subsequent inserts incremented properly.

 

[TysonLPrice]

This is what it scripted:

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.Tyson_TestIndentity ADD
	TestIdentity int NOT NULL IDENTITY (1, 1)
GO
COMMIT

 

[bborissov]

Try this (MAKE SURE YOU HAVE A PRETTY GOOD BACKUP FIRST)

ALTER TABLE dbo.Tyson_TestIndentity ADD
    TestIdentity int NOT NULL IDENTITY (1, 1)
GO
ALTER TABLE Tyson_TestIndentity DROP COLUMN OldColumnName
GO
sp_rename 'dbo.Tyson_TestIndentity.TestIdentity ', 'OldColumnName', 'COLUMN'
GO

That is if you wan to have a NEW values in identity column. If you want to keep your old values you have to add one more batch

ALTER TABLE dbo.Tyson_TestIndentity ADD
    TestIdentity int NOT NULL IDENTITY (1, 1)
GO
SET IDENTITY_INSERT Tyson_TestIndentity ON
UPDATE Tyson_TestIndentity SET TestIdentity = OldColumnName
SET IDENTITY_INSERT Tyson_TestIndentity OFF
GO
ALTER TABLE Tyson_TestIndentity DROP COLUMN OldColumnName
GO
sp_rename 'dbo.Tyson_TestIndentity.TestIdentity', 'OldColumnName', 'COLUMN'
GO

NOT TESTED!!!!!!!!!!!!!!!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Bonediggler1]

Thanks all - I will try these out and get back to you.

 

[Bonediggler1]

Bborissov - I used your first example and it worked great.

Thanks again