Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #11 (permalink)  
Old February 2nd, 2007, 04:38 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
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
Reply With Quote
  #12 (permalink)  
Old February 7th, 2007, 02:02 AM
Registered User
 
Join Date: Feb 2007
Location: new york, new york, USA.
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.

Reply With Quote
  #13 (permalink)  
Old April 17th, 2007, 10:33 AM
Registered User
 
Join Date: Apr 2007
Location: , , .
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.
Reply With Quote
  #14 (permalink)  
Old April 18th, 2007, 12:28 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
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
Reply With Quote
  #15 (permalink)  
Old September 2nd, 2007, 01:28 PM
Registered User
 
Join Date: Sep 2007
Location: Dhaka, , Bangladesh.
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)


Reply With Quote
  #16 (permalink)  
Old September 3rd, 2007, 08:19 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
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
Reply With Quote
  #17 (permalink)  
Old September 7th, 2007, 10:03 PM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
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
Reply With Quote
  #18 (permalink)  
Old September 10th, 2007, 07:38 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 08:57 AM.


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