Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to alter the existing id values to idetity(1,1) Expand / Collapse
Author
Message
Posted Thursday, August 29, 2013 8:36 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 5:02 AM
Points: 72, Visits: 247
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
Post #1489753
Posted Thursday, August 29, 2013 8:45 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:49 AM
Points: 2,019, Visits: 3,469
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.
Post #1489759
Posted Thursday, August 29, 2013 8:46 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 7:08 AM
Points: 277, Visits: 329
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...
Post #1489761
Posted Thursday, August 29, 2013 9:00 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:49 AM
Points: 2,019, Visits: 3,469
Just wishful thinking
Post #1489765
Posted Thursday, August 29, 2013 9:22 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:40 PM
Points: 2,214, Visits: 3,335
ALTER TABLE dbo.tblemployee
ADD ident int IDENTITY(1, 1) NOT NULL

Edit: Added "NOT NULL"


SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1489772
Posted Thursday, August 29, 2013 9:35 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:49 AM
Points: 2,019, Visits: 3,469
Ha, I did not know you could do that!
Post #1489778
Posted Thursday, August 29, 2013 8:55 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:02 PM
Points: 35,397, Visits: 31,955
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1489984
Posted Friday, August 30, 2013 3:10 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:47 AM
Points: 1,926, Visits: 2,352
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/
Post #1490035
Posted Friday, August 30, 2013 9:53 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:40 PM
Points: 2,214, Visits: 3,335
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1490212
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse