adding an identity column to existing table

I removed all constraints in order to load a bunch of data into a table, now I'm wondering if I can add an identity column to this table which does contain data or if I have to create a new table with the identity column and insert the data into that.

thx

Kat

The alter table didn't work. Just created a new table with the identity column and inserted into that. So never mind... Unless there is a different way to do this. I know, it has been a while for me.

Kat

|||

It should:

create table test
(
value varchar(10)
)
go
insert into test
select 'a'
union all
select 'b'
union all
select 'c'
go
select *
from test
/*
value
-
a
b
c
*/

alter table test
add testId int identity
go
select *
from test

Can you post the syntax/error you got if it doesn't work for you?

|||

Hi Louis,

This did work. I think my syntax was the problem when altering the table, which I discarded. I was trying to add a primary key constraint at the same time and my syntax was obviously messed up. I followed the BOL syntax for altering a table, adding an identity column along with a primary key constraint. It didn't appear to say that this was not possible unless I read the 'alter table' section incorrectly. Can you tell me if this is possible and what the correct 'alter table' add identitiy, and make it a primary key at the same time?

thx,

Kathleen

|||

Just add PRIMARY KEY:

create table test
(
value varchar(10)
)
go
insert into test
select 'a'
union all
select 'b'
union all
select 'c'
go
select *
from test
/*
value
-
a
b
c
*/

alter table test
add testId int constraint PKTest identity primary key
go
select *
from test

The constraintPKTest in bold italics is optional

|||

Even though we allow the syntax below:

alter table test
add testId int constraint PKTest identity primary key

I think it is a bug to allow the IDENTITY keyword in between the constraint definition. The correct syntax is:

alter table test
add testId int identity constraint PKTest primary key

I will file a bug internally for the invalid syntax.

|||That was a mistake. I can't believe I didn't notice that. I also can't believe it compiled :)