Error in Alter Table Statement

Hi,
I tried to alter one column as Identity colum. I executed the following
statement.
ALTER TABLE [dbo].[Table_1] ALTER COLUMN [Col_1] [smallint] IDENTITY (1, 1)
NOT NULL
But it gives the following error
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'IDENTITY'.
The table has some rows. I do not want to re-set the identity value. I can
able to do this in Enterprise Manager. But I want to do in T-Sql statement.
Pls help me
Thanx
San
You cannot do that
CREATE TABLE #Test
(
col INT NOT NULL
)
GO
INSERT INTO #Test VALUES (20)
GO
ALTER TABLE #Test ADD col1 INT IDENTITY(1,1)
GO
SET IDENTITY_INSERT #Test ON
GO
INSERT INTO #Test(col1)SELECT MAX(col) FROM #Test
GO
SET IDENTITY_INSERT #Test OFF
GO
INSERT INTO #Test (col) VALUES (100)
GO
SELECT * FROM #Test
"San" <San@.discussions.microsoft.com> wrote in message
news:290563A4-569C-4136-80D7-6EEC6DDE7463@.microsoft.com...
> Hi,
> I tried to alter one column as Identity colum. I executed the following
> statement.
> ALTER TABLE [dbo].[Table_1] ALTER COLUMN [Col_1] [smallint] IDENTITY (1,
1)
> NOT NULL
> But it gives the following error
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'IDENTITY'.
> The table has some rows. I do not want to re-set the identity value. I can
> able to do this in Enterprise Manager. But I want to do in T-Sql
statement.
> Pls help me
> Thanx
>
|||Hi,
If you want to do this in t-sql you must follow the steps:
1) Create temporary table like CREATE TABLE dbo.Tmp_Table_1
(
Column_1 smallint NOT NULL IDENTITY (1, 1),
--,
) ON [PRIMARY]
2) SET IDENTITY_INSERT dbo.tmp_Table_1 ON
3) EXEC('INSERT INTO dbo.tmp_Table_1 (Col_1,--)
SELECT Col_1, -- FROM dbo.Table_1 TABLOCKX')
4) SET IDENTITY_INSERT dbo.Tmp_Table_1 OFF
5) drop table dbo.Table_1
6) sp_rename 'dbo.Tmp_Table_1','dbo.Table_1'
If you have indexes, constraints etc.. add a step to create them as well on
new table
I did not know any t-sql statement that alters a column to identity. If you
capture a trace via profiler while altering the column in Enterprise Manager
you will find out that it performs the same logic.
Hope this helps.
"San" wrote:

> Hi,
> I tried to alter one column as Identity colum. I executed the following
> statement.
> ALTER TABLE [dbo].[Table_1] ALTER COLUMN [Col_1] [smallint] IDENTITY (1, 1)
> NOT NULL
> But it gives the following error
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'IDENTITY'.
> The table has some rows. I do not want to re-set the identity value. I can
> able to do this in Enterprise Manager. But I want to do in T-Sql statement.
> Pls help me
> Thanx
>