drop the identity column - 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 Administration

drop the identity column

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2009-07-14 : 15:35:07

hi
is there any way to drop the identity property of a column using T-Sql?

Arnav
Even you learn 1%, Learn it with 100% confidence.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-14 : 15:43:27

You can add a new column and update it with the values of your identity column and I think you know whatare now the following steps...

Short answer is
NO - not easy.

Do it via ssms and let ssms create a script to see how it is going on there...

No, you're never too old to Yak'n'Roll if you're too young to die.

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2009-07-14 : 16:13:14

create table mydata (
rid int identity(1,1),
dat varchar(2000),
CONSTRAINT MYDAYA_PK PRIMARY KEY (rid)

)
go

insert into mydata (dat)
select 'row 1' union all select 'row 2'
go

select * from mydata
go

create table mydata_TMP (
rid int,
dat varchar(2000),
CONSTRAINT MYDAYA_TMP_PK PRIMARY KEY (rid)

)

insert into mydata_TMP
select * from mydata

drop table mydata

sp_rename 'mydata_TMP', 'mydata'

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-14 : 16:20:31

That's what SSMS is doing

No, you're never too old to Yak'n'Roll if you're too young to die.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2009-07-14 : 16:35:16

i'm trying to create a table using select * into
how can i aviod identity property to the new table?

is it possible to create a new table without copying the identity property?

Arnav
Even you learn 1%, Learn it with 100% confidence.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-15 : 01:36:51

If you are not using * you can do:
select id+0 as id, col2,col3 into table2 from table1
In this case the id in the new table isn't identity.

Fred

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources