How do you drop columns from a table

R.D. Raab & Company

unread,

Oct 2, 1997, 3:00:00 AM10/2/97

to

I can not find any documentation on dropping a column from a table. I have
a few columns that are not correct. I would like to take them out and
redefine them. Is this possible?

John Shaw

unread,

Oct 2, 1997, 3:00:00 AM10/2/97

to

I don't believe so. You can create a new table and write a SELECT INTO
statement to populate it then drop the old table. You can modify
constraints and defaults bound to the column.

GLB...@hub.doh.wa.gov

unread,

Oct 3, 1997, 3:00:00 AM10/3/97

to

In article <01bccee4$f45a5f80$45056acf@mheilman1>,

"R.D. Raab & Company" <mhei...@rdraab.com> wrote:
>
> I can not find any documentation on dropping a column from a table. I have
> a few columns that are not correct. I would like to take them out and
> redefine them. Is this possible?

THE ONLY WAY I HAVE FOUND, IS TO DROP AND RE-CREATE THE TABLE. THIS CAN
CAUSE PROBLEMS WITH TRIGGERS, CONSTRAINTS AND REFERENTIAL INTEGRITY. I
FIRST, DROP ALL CONSTRAINTS AND TRIGGER TO THE TABLE. SECOND, RENAME THE
TABLE. THIRD, CREATE THE TABLE WITH NEW DEFINITIONS. FOURTH, APPEND ALL
DATA FROM OLD TABLE TO NEW TABLE. FIFTH, CREATE ALL CONSTRAINTS AND
TRIGGERS FOR THE NEW TABLE. FINALLY, DROP THE OLD TABLE. REMEMBER TO ALSO
REBUILD THE PERMISSIONS TO THE NEW TABLE WHEN DONE WITH THE CHANGES.

-------------------==== Posted via Deja News ====-----------------------
http://www.dejanews.com/ Search, Read, Post to Usenet

Lord Willy

unread,

Oct 5, 1997, 3:00:00 AM10/5/97

to

R.D. Raab & Company <mhei...@rdraab.com> wrote in article
<01bccee4$f45a5f80$45056acf@mheilman1>...

> I can not find any documentation on dropping a column from a table. I
have
> a few columns that are not correct. I would like to take them out and
> redefine them. Is this possible?

BCP all the table's data to a CSV file, drop the table, recreate the table
with the new column, BCP the data in from the file, and you will be all
set.

Make backups first...
--
oxxxxxx)|[::::::::Lord::Willy:::::::::::::::>

Peter

unread,

Oct 7, 1997, 3:00:00 AM10/7/97

to

GLB...@hub.doh.wa.gov wrote in article <8758957...@dejanews.com>...
> In article <01bccee4$f45a5f80$45056acf@mheilman1>,

> "R.D. Raab & Company" <mhei...@rdraab.com> wrote:
> > I can not find any documentation on dropping a column from a table. I
have
> > a few columns that are not correct. I would like to take them out and
> > redefine them. Is this possible?

> THE ONLY WAY I HAVE FOUND, IS TO DROP AND RE-CREATE THE TABLE.
> THIS CAN CAUSE PROBLEMS WITH TRIGGERS, CONSTRAINTS AND
> REFERENTIAL INTEGRITY.
> I FIRST, DROP ALL CONSTRAINTS AND TRIGGER TO THE TABLE.
> SECOND, RENAME THE TABLE.
> THIRD, CREATE THE TABLE WITH NEW DEFINITIONS.
> FOURTH, APPEND ALL DATA FROM OLD TABLE TO NEW TABLE.
> FIFTH, CREATE ALL CONSTRAINTS AND TRIGGERS FOR THE NEW TABLE.
> FINALLY, DROP THE OLD TABLE.
> REMEMBER TO ALSO REBUILD THE PERMISSIONS TO THE NEW TABLE

Or Use Visual Interdev. It will allow you to change a SQL Server table's
definition and do all of that work for you behind the scenes (Yes, it all
happens) or you can save a generated script and use it later. Trust me, it
works.

-Peter

--
Peter W. DeBetta
pe...@milori.com
http://www.milori.com

"Two roads diverged in a wood, and I-
I took the one less traveled by,
And that has made all the difference."

CIMTEK

unread,

Oct 13, 1997, 3:00:00 AM10/13/97

to

>

What is Visual Interdev? Where to find it?

Huang