how to change an existing table column to autoincrement with out affecting the data

[ramyareddy]

Hi All,

I am using MSSQL 2000 and 2005.
i have a table memberSecurity with security numer as primary key.
I have some data in the table already.
Now i want to make secNo as auto increment and primary key with out affecting the existing data.

Is there any script that i can run for this?

Thanks,
Ramya

[gahoi]

Hi All,

I am using MSSQL 2000 and 2005.
i have a table memberSecurity with security numer as primary key.
I have some data in the table already.
Now i want to make secNo as auto increment and primary key with out affecting the existing data.

Is there any script that i can run for this?

Thanks,
Ramya

hi
in table property
below

increment property select and change no to yes

[ramyareddy]

Hi,

I CAN DO THAT, but i want query so that i can run it from query analyser.

any ideas.....


Ramya

[ck9663]

Hi,

I CAN DO THAT, but i want query so that i can run it from query analyser.

any ideas.....


Ramya

alter table. add an integer-IDENTITY column

[ramyareddy]

alter table. add an integer-IDENTITY column

Hi,

The column is already there in the table. Only thing i have to do is make it auto increment by running some script.

Ittried with this alter table memberSecurity alter column secNo int IDENTITY(1,1)

it is giving error Incorrect syntax near the keyword 'IDENTITY'.

Please tell me where i am wrong, i want to do this by running some script, not from enterprise manager.

Thanks,
Ramya.

[rokib hasan]

SoftEng

Try This:
Alter table memberSecurity add column secNo int IDENTITY(1,1)
primary key

[rokib hasan]

SoftEng

sorry for previous reply.
try this:

Alter table memberSecurity add secNo int IDENTITY(1,1)
primary key