kewldudehere, By the way I am not using INSERT INTO as I said in my previous post, I use SELECT INTO, which creates a table and inserts the rows matching criteria. Anyways, thanks for the reply.

Cheers.

_________________________
- Vijay G
Strive for Perfection

kewldudehere, this is soo cool!
I was looking for that. Before I tried to update status column but was rejected with message saying status is computed column. Than I found your post.
It did not work right away. Than later it worked beautefully. Do you thing there is any threat for corruption?
I ran all 3 dbcc's and all came back clean.

You need to run a follow script:

table2 has a identity column property that you want remove.

drop table table1
insert into table1 select id_column as xx, * from table2
alter table table1 drop column xx

I ran this code in SQL Server 2000.

I hope help.

michelnassib,

Well, from the statements that you posted, it looks like you are losing the column itself. My question wasn't that. I could drop off the identity column straight away, if I wanted to lose that. All I wanted is to remove off the IDENTITY PROPERTY of a column in a table, not dropping the Identity column itself.

Thanks for the post anyways.
cheers.

_________________________
- Vijay G
Strive for Perfection

hi,
I hv faced the same problem that u hv asked for. Here are a way to do that.

Code That Drops the IDENTITY Property from an Existing Column

--If other tables’ foreign keys point to the IDENTITY column, drop them. Here, the script drops
--the foreign key pointing to Orders2.OrderID from OrderDetails2.
ALTER TABLE OrderDetails2
  DROP CONSTRAINT FK_OrderDetails2_Orders2

--If a primary key exists on the IDENTITY column, drop the primary key constraint the way this
--script drops the primary key from Orders2.
ALTER TABLE Orders2
  DROP Constraint PK_Orders2

--Add another column with the same data type as the IDENTITY column to Orders 2 and allow
--NULLs.
ALTER TABLE Orders2
  ADD new_OrderID int NULL

--Update the new column with the values of the IDENTITY column.
UPDATE Orders2
  SET new_OrderID = OrderID

--If the new column doesn’t permit NULLs, alter the column to NOT NULL.
ALTER TABLE Orders2
  ALTER COLUMN new_OrderID int NOT NULL

--Drop the IDENTITY column.
ALTER TABLE Orders2
  DROP COLUMN OrderID

--Rename the new column to the dropped IDENTITY column’s name.
EXEC sp_rename 'Orders2.new_OrderID', 'OrderID', 'COLUMN'

--If a primary key exists on the new column, recreate the key. In this case you recreate the
--primary key on Orders2.
ALTER TABLE Orders2
  ADD CONSTRAINT PK_Orders2 PRIMARY KEY(OrderID)

--On other tables, recreate any foreign keys that originally pointed to the old IDENTITY column
--and point them to the new column. Here, you recreate the foreign key on OrderDetails2.
ALTER TABLE OrderDetails2 WITH NOCHECK
  ADD CONSTRAINT FK_OrderDetails2_Orders2
    FOREIGN KEY(OrderID)
    REFERENCES Orders2(OrderID)

neeloy,

Thanks for the update. I understand that your approach is to move the data to another column within the same table and then drop the column with Identity set. This is something similar to dropping the table after moving the entire table data in to another.

I haven't tried it yet, will try it later in my free time.

Thanks again.
Cheers.

_________________________
- Vijay G
Strive for Perfection

Vijay... you're not losing the column... it's still there as part of the *. It's just a trick (a good one, too!) to get the SELECT INTO to work without having to list all the columns. It copies the IDENTITY column twice (only the first of which will have the IDENTITY property) and then drops the first occurance of the column.

--Jeff Moden

Jeff,

Thanks for pointing it out, It was my bad, I somehow thought that we are dropping the identity column and losing it completely.

Cheers.

_________________________
- Vijay G
Strive for Perfection