hi Experts,
when i alter non identity column to identity column
using this Query
alter table testid
alter column test int identity(1,1)
then i got this error message
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'identity'.
thanks
alter non identity column to identity column
August 12th, 2009 6:19am
You cannot alter any column to set identity value.
means you cannot create identity property by altering column.
only solution is to create a new column settting identity value true and then remove previous column.
like
alter table tablename
add columname int identity(1,1) not null
means you cannot create identity property by altering column.
only solution is to create a new column settting identity value true and then remove previous column.
like
alter table tablename
add columname int identity(1,1) not null
Free Windows Admin Tool Kit Click here and download it now
August 12th, 2009 6:24am
I think one of way to alter column as identity property. Please try it out on test environment first.
- Go to Tools>Designers>Table and Designers and uncheck "Prevent Saving Changes That Prevent Table Recreation."
- Modify Account table -> Set Identity property to True
DECLARE @MaxId INT;
SET @MaxAId = (SELECT MAX(Id) + 1 FROM TABLENAME);
PRINT @MaxId
DBCC CHECKIDENT('TABLENAME', RESEED, @MaxId);
PRINT 'Complete'
You can always turn the function back on after you are happy with table structures change.
July 10th, 2015 2:17am