How to alter int column to identity

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

SQL Server 2008 Forums

Transact-SQL (2008)

How to alter int column to identity

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-09-20 : 14:35:54

How can i alter an int column to identity with seeding to start from 1361 with the increments of 1.
Is it the right way:
alter table table_orders alter column ord_id [int] IDENTITY(1,1361) NOT NULL;

thank you very much for the helpful info.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-09-20 : 14:38:47

You can't add the identity property to an existing column. You'll have to add a new column with identity, drop the old column, then rename the new column. Or insert it to a new table with the correct settings, then rename the table.

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-09-20 : 14:48:26

How can i insert all these existing ID's as is, i have almost 1359 rows, with ID's.

By mistake i deleted 4 rows from this table, now i wanted to put those rows back.

when i do that the identity column ids will change for that reason.

I created a new column "ID1" and put all ids to that column and then deleted the existing Identity column.

now, wanted to make this newly created column as identity column.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-09-20 : 15:30:36

You could have re-inserted those rows using IDENTITY_INSERT and it would have used the values you specify. More information here:

http://msdn.microsoft.com/en-us/library/aa259221.aspx

As I said, you can't add identity to an existing column, you'll have to create a new one with the identity property. Your best bet is to create a new table with all the needed columns, then use IDENTITY_INSERT on the new table, like so:

CREATE newTable (ID int not null IDENTITY(1,1), ...other column defs...)
SET IDENTITY_INSERT newTable ON
INSERT newTable(ID, col1, col2, etc.) SELECT ID, col1, col2, etc. FROM oldTable
SET IDENTITY_INSERT newTable OFF

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-09-20 : 15:59:38

Thank you very much Rob....

I found few of your old posts and found the following:

create table #t (id int identity (1,1),col1 int)
insert into #t
select 2 union
select 4

select * from #t

SET IDENTITY_INSERT #t ON -- << read about SET IDENTITY_INSERT in Books On Line
INSERT INTO #t(id,col1)
SELECT 5,1

SET IDENTITY_INSERT #t OFF
INSERT INTO #t
select 7

select * from #t

this helped.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources