how to alter the existing id values to idetity(1,1)

Question

how to alter the existing id values to idetity(1,1)

dastagiri16

Hall of Fame

Points: 3544
More actions
August 29, 2013 at 8:36 am

#395393

hi,
I have created the table like
create table tblemployee(id int,name varchar(10),gender int)
the table does not have any records..
now how to add identity seed and increment values like identity(1,1) ..
i have tried it is working from table design how to write T-sql query for that.
Thanks,
Giri

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gazareth One Orange Chip Points: 27737 More actions · Answer 1

You can't add the identity property to an existing table/column.

If you script out the change through the table designer, you'll see the script actually creates a new table with the identity property, copies the data, deletes the original table, and then renames the new table to the original name.

If there's no data in the table you can safely drop and recreate it, be sure to include any referenced or referencing constraints though.

logitestus SSCrazy Points: 2928 More actions · Answer 2

~~You could try something along the lines of:~~

~~ALTER TABLE XXX~~

~~ALTER COLUMN ID INT IDENTITY(1,1)~~

~~That would modify your Id column to being an identity column with a seed value of 1 and an increment value of 1.~~

Please pay no attention to the idiot walking through... 🙂

Gazareth One Orange Chip Points: 27737 More actions · Answer 3

Gazareth

One Orange Chip

Points: 27737

August 29, 2013 at 9:00 am

#1646022

Just wishful thinking 🙂

ScottPletcher SSC Guru Points: 101101 More actions · Answer 4

ALTER TABLE dbo.tblemployee

ADD ident int IDENTITY(1, 1) NOT NULL

Edit: Added "NOT NULL"

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Gazareth One Orange Chip Points: 27737 More actions · Answer 5

Gazareth

One Orange Chip

Points: 27737

August 29, 2013 at 9:35 am

#1646032

Ha, I did not know you could do that!

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 6

ScottPletcher (8/29/2013)
ALTER TABLE dbo.tblemployee
ADD ident int IDENTITY(1, 1) NOT NULL
Edit: Added "NOT NULL"

The NOT NULL is great visual confirmation that the column will be NOT NULL, but the IDENTITY property will make the column NOT NULL if you wanted to leave the NOT NULL off.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kapil_kk SSC-Insane Points: 21316 More actions · Answer 7

ScottPletcher (8/29/2013)
ALTER TABLE dbo.tblemployee
ADD ident int IDENTITY(1, 1) NOT NULL
Edit: Added "NOT NULL"

You are adding a new column with IDENTITY property and it works..

The thing is that we cant add IDENTITY property to any existing column of table...

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ScottPletcher SSC Guru Points: 101101 More actions · Answer 8

kapil_kk (8/30/2013)
ScottPletcher (8/29/2013)
ALTER TABLE dbo.tblemployee
ADD ident int IDENTITY(1, 1) NOT NULL
Edit: Added "NOT NULL"
You are adding a new column with IDENTITY property and it works..
The thing is that we cant add IDENTITY property to any existing column of table...

No, you simply cannot do that directly.

You would have to export the table, recreate the table definition, then import with IDENTITY_INSERT ON to use an existing column as an IDENTITY value.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".