i have the 2 script

one alters the column to have primary key
the other one adds a new id field that is identity
now how can i add both together
so that it alters a field to primary key and identity

alter table test_primarykey
 add CONSTRAINT [PK_test_primarykey] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)



ALTER TABLE test_primarykey add
id INT IDENTITY(1,1)
CONSTRAINT pk_id PRIMARY KEY

First one alters the column to be a primary key, the second one adds a new column with identity set and constraints it as primary key. How can you merge both? In one case the column exist and another the column is added, both the scripts use same column name - ID. Doest it not contradict to what you are trying to do?

Moreover, You can't use ALTER TABLE to modify a column to be identity. It can be done only when adding the column, AFAIK...

So you got to follow the second script. The right things is to, create a new table with identity/primarykey column of similar structure and do an insert into and copy all the rows from old table to the new one.

_________________________
- Vijay G
Strive for Perfection