How to drop IDENTITY - 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

SQL Server 2000 Forums

Transact-SQL (2000)

How to drop IDENTITY

Author

Topic

real_pearl
Posting Yak Master

106 Posts

Posted - 2004-07-23 : 03:43:26

HOw can we drop the identity property of a column?

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-07-23 : 04:05:03

create table Hello(col1 int identity(1, 1), adesc varchar(100))

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_Hello
(
col1 int NOT NULL,
adesc varchar(100) NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.Hello)
EXEC('INSERT INTO dbo.Tmp_Hello (col1, adesc)
SELECT col1, adesc FROM dbo.Hello TABLOCKX')
GO
DROP TABLE dbo.Hello
GO
EXECUTE sp_rename N'dbo.Tmp_Hello', N'Hello', 'OBJECT'
GO
COMMIT

Duane.

Kristen
Test

22859 Posts

Posted - 2004-07-23 : 04:14:55

I don't think you can. Create a temporary table with the same columns, but no identity, copy all the data across, delete the original, rename the Temporary to the Original name

(If you do it in Enterprise Manager it will take care of it for you, along these lines)

Uou could also RENAME the existing column.
Then add a new column, with the original name.
UPDATE to set the new column to the old columns values.
Then ALTER TABLE MyTable DROP COLUMN MyOldID,
Then look after the value in that column yourself from then onwards.

Beware that this method creates inefficient storage utilisation within SQL's files, so not recommended for very large tables - unless you are planning on the Temp Table - Copy Across - Drop original/Rename method as a follow-on on the relatively near future.

Kristen

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-07-23 : 04:16:23

My solution was scripted from EM

Duane.

real_pearl
Posting Yak Master

106 Posts

Posted - 2004-07-23 : 04:36:15

If I am creating a temp table using

SELECT * INTO ##tblTemp FROM A where a.col_id IS NULL

and and I dont know how many columns would A will be containg, because A is created dynamically and primary key column is IDENTITY column. I just get the structure of the table A.

I later insert the data in ##tblTemp which gives error because of the IDENTITY column. that's y i want to remove the identity property. Please let me know how to do this?

Kristen
Test

22859 Posts

Posted - 2004-07-23 : 05:51:22

I wouldn't have guessed that SELECT * INTO ##TEMP would have preserved the IDENTITY attribute on that column, Ho!Hum!

You'll have to beef up the way you do this then. Interrogate the system tables to work out what the structure of the table is and then CREATE it dynamically, and then INSERT, or INSERT first and then modify the IDENTITY column.

You could use SET IDENTITY_INSERT but that will require a list of columns, isntead of SELECT *, so you're pretty much back to square one.

Kristen

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-23 : 13:16:29

SQL only generates 1 identity column. Include it twice and drop the identity column afterwards.

select id as justanumber, * into newtable from mytable

alter table newtable drop column id

--Ken
Your Kung-Fu is not strong. -- 'The Core'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources