Data Warehouse Architect (IL) Next Step Systems US-IL-Chicago Post A Job | Post A Resume

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

Read Other SQL Guru Questions

Windows Internet Technology | ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article