ALTER COLUMN HELP NEEDED

[jschaddock]

Hello everyone,

I have a problem trying to alter a column in a SQL table.
The table is called fees and has a column called ID in it that is numeric datatype. What I am trying to do is to alter this column to be an identity column with increments of one. I know that by running the code

ALTER TABLE Fees DROP COLUMN ID
ALTER TABLE Fees ADD ID numeric(18,0) Identity(1,1) NOT NULL

the column will be dropped and then recreated as an identity column but I am told by my boss that we're not allowed to drop the original column.
So instead,I've been trying to run the code

ALTER TABLE Fees ALTER COLUMN ID numeric(18,0) Identity(1,1) NOT NULL

but I keep getting syntax errors.

Could someone please tell me how I can alter this column to be an identity column without having to drop and recreate it?

Thanks

 

[moonshadow]

Believe it or not, the trick is drop & recreate the table. Create a new table with the format you want, run INSERT INTO to copy data from old table to new (with SET INSERT_IDENTITY ON), drop the old table, rename the new table using sp_rename. If you've got SQL 7, it will generate a script for you that does all this, when you edit the table in Enterprise Manager. If you have any referential integrity, the foreign keys will have to all be dropped before you can drop the old table, then recreated on the new one once you have renamed it. Good luck

 

[tlbroadbent]

Here is an alternative method that should work in SQL 7 or 2000. I have only tested it in SQL 2000.

--Add new identity column
Alter Table Fees add id2 numeric(18,0) identity

--Rename old ID column
EXEC sp_rename 'Fees.id', 'idold', 'COLUMN'

--Rename new ID column
EXEC sp_rename 'Fees.id2', 'id', 'COLUMN' Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.