Alter column
-
Hi,
How to change an existing integer column to an identity column (in SQL Server - without dropping and recreating the column)?
the below line returns error:
alter table
alter column [id] int identity(1,1)
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'identity'.
identity
-
AFAIK, you can't.
Identity columns has to be created as new, they cannot be altered from old columns. (from the top of my head, though - haven't confirmed if I remember this correctly)
/Kenneth
-
try with the following code
if you have constraints don't forget to add them
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
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
CREATE TABLE dbo.Tmp_userparameters
(
id int NOT NULL IDENTITY (1, 1) NOT FOR REPLICATION,
....
 
ON [PRIMARY]GO
SET IDENTITY_INSERT dbo.Tmp_userparameters ON
GO
IF EXISTS(SELECT * FROM dbo.userparameters)
EXEC('INSERT INTO dbo.Tmp_user parameters (id,...)
SELECT id,... FROM dbo.userparameters TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_userparameters OFF
GO
DROP TABLE dbo.userparameters
GO
EXECUTE sp_rename N'dbo.Tmp_user parameters', N'userparameters', 'OBJECT'
GO
or you can change the identity property from Enteprise Manager
-
This is not the answer to the posters question.
Here you do create a new column with identity property, and EM will also create a new column - the question was if it was possible to alter an existing column. (which is no - it's not)
/Kenneth
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply