Setting Identity - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Setting Identity

Author

Topic

gemispence
Yak Posting Veteran

71 Posts

Posted - 2006-02-28 : 12:36:18

I'm trying to write a small script that sets the identity on 7 tables that don't have an identity set yet. Can someone advise on the syntax?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-02-28 : 12:44:46

alter table <tablename> add column <columnname> int identity (1,1)

or

alter table <tablename> alter column <columnname> int identity (1,1)

gemispence
Yak Posting Veteran

71 Posts

Posted - 2006-02-28 : 12:50:01

Thanks, but it gives me an error saying incorrect syntax near Identity as shown below. I want it to be an int (4) data type.

alter table condofeatures alter column ID int identity (1,1)

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'identity'.

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-02-28 : 13:06:29

I didn't think you could alter an existing column to be an identity. You can add one, Rick's first statement will work, but the second won't.

I think the only way do this is create a new table and move the data.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-28 : 13:12:26

You can also use Enterprise Manager but what EM will do in the background basically the same thing that JoeNak said.

Be One with the Optimizer
TG

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-28 : 13:38:24

Make the change in Enterprise Manager but do not save it. Instead, click the save change script button. View the code in Query Analyzer. Notice how it creates a new table with the desired layout, copies the data into this new table, drops the old table, renames the new table to the old table, then applies constraints, indexes, etc...That's what it takes to do what you want if you are modifying an existing column to IDENTITY.

Tara Kizer
aka tduggan

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-03-01 : 07:12:16

quote:
Originally posted by JoeNak

I didn't think you could alter an existing column to be an identity. You can add one, Rick's first statement will work, but the second won't.

I think the only way do this is create a new table and move the data.

Didn't even think about that..

pallavi_020
Starting Member

1 Post

Posted - 2007-03-10 : 01:15:07

First Check if u have primary key on those column . Beacuse I had same problem and my column was having primary key

if u hv primary key set to those column

then
1. alter table xxx drop constraint PK_xxx
2. ALTER TABLE xxx DROP COLUMN id
3. alter table xxx add id int identity (1,1) NOT NULL
4. alter table xxx add CONSTRAINT [PK_xxx] PRIMARY KEY (id)

it worked for me .. u can try

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources