alter table and removing IDENTITY property

Question

Post reply

alter table and removing IDENTITY property

ckempste

SSCoach

Points: 17983
More actions
July 26, 2001 at 11:51 pm

#77396

Hi all
Im having some trouble working out the syntax to alter a table column and remove the IDENTITY property from it. I have a large data move coming up and need to "turn off" identity of some of the tables, insert the data, and reapply the identity after the load.
Ideas?
Cheers
Chris

Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
June 21, 2017 at 12:48 am

This was removed by the editor as SPAM

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

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

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 1

SET IDENTITY_INSERT tablename ON

INSERT INTO tablename .......

SET IDENTITY_INSERT tablename OFF

Note that only one table at a time (per session) can have IDENTITY_INSERT on.

Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 2

Be sure when you do this, you supply a specific identity value.

Steve Jones

steve@dkranch.net

ckempste SSCoach Points: 17983 More actions · Answer 3

Hi guys

Hmmm.. yeh, knew this one and wasnt what I was after . Basically I wanted to permanently remove IDENTITY from a column via an ALTER DATABASE statement. Is this only possible via dropping the table all together?

Cheers

Chris

Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"

Andy Warren SSC Guru Points: 119902 More actions · Answer 4

Hey, I just profiled it a couple times, then changed the identity setting via EM several times - looks like it does drop the table each time, but at least you don't have to do the work! Possibly because its late Im just not seeing it, but the only alter I see are those performed on the "new" table to apply constraints to it. That sorta makes sense since identity is treated like a constraint, but where is the "drop identity" or whatever?

Anyone have any insight to offer?

Andy

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

ckempste SSCoach Points: 17983 More actions · Answer 5

Andy

Hey there Andy, check this out from profiler...my table "aaaaa" didnt have identity enabled, then enabled it and got this:

CREATE TABLE dbo.Tmp_aaaaa

(

aaa int NOT NULL IDENTITY (1, 1),

bbb char(10) NULL

) ON [PRIMARY]

go

SET IDENTITY_INSERT dbo.Tmp_aaaaa ON

go

IF EXISTS(SELECT * FROM dbo.aaaaa)

EXEC('INSERT INTO dbo.Tmp_aaaaa (aaa, bbb)

SELECT aaa, bbb FROM dbo.aaaaa TABLOCKX')

go

SET IDENTITY_INSERT dbo.Tmp_aaaaa OFF

DROP TABLE dbo.aaaaa

exec sp_cursoropen @P1 output, N'EXECUTE sp_rename N''dbo.Tmp_aaaaa'', N''aaaaa'', ''OBJECT''

go

Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"

Andy Warren SSC Guru Points: 119902 More actions · Answer 6

Using EM?

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

ckempste SSCoach Points: 17983 More actions · Answer 7

Yep.

Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 8

If you make the change in EM and then click the "script" button (3rd from left) you should see this. Many things you do in EM are only possible through dropping a table and this is usually how EM scripts it.

Steve Jones

steve@dkranch.net