To read the article online, visit http://www.4GuysFromRolla.com/webtech/sqlguru/q022900-2.shtml

The SQL Guru Answers your Questions...


Today's question comes from Jeremy C.:

SQL 6.5 -- how do you delete a column from a table?

Jeremy C.

Jeremy,

Let's assume your original table is called foo. To remove a column from foo, you'll need to perform the following steps:

  • Create a new table, say, foo_new.
  • Use an INSERT..SELECT to copy the data from foo to foo_new.
  • Drop foo.
  • Rename foo_new to foo: EXEC sp_rename foo_new, foo

That's all there is to it. If there is a lot of data in the original table, be careful that your log is sized appropriately, batch the inserts, or bcp the data out of the original table and back into the new table.

Or consider upgrading to SQL 7.0. ALTER TABLE DROP COLUMN sure is nice! :)

Sean


Article Information
Article Title: SQL Guru: Removing a Column from a Table
Article Author: Scott Mitchell
Article URL: http://www.4GuysFromRolla.com/webtech/sqlguru/q022900-2.shtml


Copyright 2017 QuinStreet Inc. All Rights Reserved.
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers