IDENTITY 1

[robF]

does anyone know how to alter a column that is already populated to be an identity column?

ALTER TABLE tblName Alter COLUMN [ID] [int] IDENTITY(1,1) NOT NULL

this gives this error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'IDENTITY'.

 

[TomSark]

The altered column cannot be:
* A column with a text, image, ntext, or timestamp data type.
* The ROWGUIDCOL for the table.
* A computed column or be used in a computed column.
* A replicated column.
* Used in an index, unless the column is a varchar or varbinary data type, the data type is not changed, and the new size is equal to or larger than the old size.
* Used in statistics generated by the CREATE STATISTICS statement. First remove the statistics using the DROP STATISTICS statement. Statistics automatically generated by the query optimizer are automatically dropped by ALTER COLUMN.
* Used in a PRIMARY KEY or [FOREIGN KEY] REFERENCES constraint.
* Used in a CHECK or UNIQUE constraint, except that altering the length of a variable-length column used in a CHECK or UNIQUE constraint is allowed.
* Associated with a default, except that changing the length, precision, or scale of a column is allowed if the data type is not changed.

My guess is that you are trying to change it's type and that you have a constraint on the column which is disallowing your alter table.

You can drop and re-add the column with this code (it will re-create your identities):
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_TABLE1
(
testid int IDENTITY(1,1) NOT NULL,
test2 char(10) NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.TABLE1)
EXEC('INSERT INTO dbo.Tmp_TABLE1(test2)
SELECT test2 FROM dbo.TABLE1 TABLOCKX')
GO
DROP TABLE dbo.TABLE1
GO
EXECUTE sp_rename 'dbo.Tmp_TABLE1', 'TABLE1'
GO
COMMIT Tom Davis
tdavis@sark.com

http://www.sarkdallas.com

 

[TomSark]

I know I didn't address your identity syntax error, so after reviewing your question, what attribute of the column are you trying to alter? Tom Davis
tdavis@sark.com

http://www.sarkdallas.com

 

[robF]

i am just trying to change the column from a normal int field to an int field with an identity.

 

[TomSark]

Ahh... You will need to add the column as in:

ALTER TABLE tblName Add [NewColumn] [int] IDENTITY(1,1) NOT NULL

It will create an identity value for you.. Tom Davis
tdavis@sark.com

http://www.sarkdallas.com

 

[TomSark]

Again, you might wish to simply drop the column and re-add it with the code example I provided earlier...
Tom Davis
tdavis@sark.com

http://www.sarkdallas.com

 

[robF]

i don't want another column, i have a column that was in another database that i loaded into this one. the column that i am trying to modify was an identity column, for some reason i couldn't load the other table into the new one as long as there was an indentity column. I had to drop the table, re-create it without the identity property on the column, now i just want to put it back on. I did all this programatically by creating dts packages with VB 'on-the-fly', i just want to make the column an identity column again. any suggestions?

 

[TomSark]

Dude,

You can't do it that way... To illustrate, go to the Diagramming tool, check the identity checkbox and save the script SQL Server generates. Then examine the script. It doesn't issue an 'alter table add identity constraint' to a column command because it ain't legal syntax...

Instead, do what I recommended....

Tom Davis
tdavis@sark.com

http://www.sarkdallas.com

 

[HallCrash]

I know this process will work in an sqlserver 2000 if you are trying to preserve the previous identity column values and alter column to make it an identity field If somehow in your replication it is needed (I know I did)
--------------------------------------------------------
SET NOCOUNT ON

CREATE TABLE #TempDBItems
(TEMPID int,TEMPVALUE varchar (100))
insert into #TempDBItems (TEMPID,TEMPVALUE)
Select Brand_ID,
Brand_name
from PT_Brands order by Brand_name
Drop Table PT_Brands
CREATE TABLE [PT_Brands] (
[Brand_ID] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
[Brand_name] [varchar] (100) COLLATE NULL
) ON [PRIMARY]
SET IDENTITY_INSERT PT_Brands ON
insert into PT_Brands (Brand_ID,Brand_name)
Select TEMPID,
TEMPVALUE
from #TempDBItems order by TEMPVALUE
SET IDENTITY_INSERT PT_Brands OFF
drop table #TempDBItems
SET NOCOUNT OFF