Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 2nd, 2007, 04:38 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old February 7th, 2007, 02:02 AM
Registered User
 
Join Date: Feb 2007
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

 
Old April 17th, 2007, 10:33 AM
Registered User
 
Join Date: Apr 2007
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old April 18th, 2007, 12:28 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old September 2nd, 2007, 01:28 PM
Registered User
 
Join Date: Sep 2007
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to neeloy
Default

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)


 
Old September 3rd, 2007, 08:19 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old September 7th, 2007, 10:03 PM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

Quote:
quote:Originally posted by happygv
 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
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
 
Old September 10th, 2007, 07:38 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
dropping the identity property of column sandeep SQL Server 2005 3 March 2nd, 2007 01:57 PM
Identity Property issue Navy1991 SQL Server 2000 2 January 6th, 2006 05:27 AM
What is @@identity property? subhashbankey SQL Server 2000 2 December 22nd, 2005 09:01 PM
Identity soccers_guy10 SQL Server 2000 3 September 2nd, 2003 07:05 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.