Drop Identity property of a column

Is there a way to remove the Identity property of a column in SQL Server 2000?

The statement:

<code>

ALTER TABLE <table name> ALTER COLUMN <column name> DROP IDENTITY

</code>

returns a syntax error.

Thank you,

Jeff

T-SQL's ALTER TABLE statement doesn't support dropping the IDENTITY property in SQL Server2000 or 7.0. Your only option for deleting an IDENTITY column is tocreate a new table structure without the IDENTITY column, then copy thedata into this structure.|||

Thanks Darrell.

Jeff

|||

One more question. Why will SQL Server 2000 allow you to delete the identity property in the designer, but not script the same change?

Jeff

|||you can delete the identity property but you would still have the numbers already generated in the column. New number wouldnt be generated though.|||

Dinakar,

I actually want to do that. I need to turn off the identity property via script, copy data into the table, then turn the identity property back on. I want all the data to stay in the identity column, just not auto-increment during the copy.

Jeff

|||

In that case you can copy all the columns xcept the Identity column into the new table. Then add the Identity column to the new table.

|||In that case you don't really need to drop the column, you're justturning it off. I thought you wanted to remove the IDENTITYcolumn forever.
|||

DarrellNorton wrote:

In that case you don't really need to drop the column, you're justturning it off. I thought you wanted to remove the IDENTITYcolumn forever.

But you can't just turn it off. With SQL Server you would have tocreate a new non-identity column, populate it with the data from theidentity column, then remove the idenitity column. If you need tohave to maintain the same column name then you have to add in somecolumn renaming goodness.

|||

Hello...

from search:
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=482&lngWId=5
do test before go to production with big rows table
i just tested with couple of rows in my test table...

-- ----------

USE pubs
GO

EXEC sp_configure 'allow update', '1'
RECONFIGURE WITH OVERRIDE
GO

DECLARE @.col varchar(128), @.table varchar(128)

-- for find identity column (if colstat =1 then identity is on)
SELECT @.col=name
FROM syscolumns
WHERE id=OBJECT_ID('pubs..test') AND (colstat & 1 <> 0)

-- SELECT @.col
IF (@.col IS NOT null) BEGIN

UPDATE syscolumns
SET colstat = colstat ^ 1
WHERE id = OBJECT_ID('pubs..test') and name = @.col

END

EXEC sp_configure 'allow update', '0'
RECONFIGURE WITH OVERRIDE
GO
-- ----------

|||But actually you can just turn the identity property off. You can't just turn it off in code. You can turn it off in the designer but not in the code. This must be a bug in SQL Server 2000 because all functionality in the table designer should be scriptable.
Jeff|||hello...
to set identity column on/off during the insertion.
this is the way,

SET IDENTITY_INSERT [database.[owner.] ] {table} { ON | OFF }
from Books Online:

Remarks

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, Microsoft? SQL Server? returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.

If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.

The setting of SET IDENTITY_INSERT is set at execute or run time and not at parse time.