.NET Answers

One of the main problems we seem to be stuck with at this point in programming history is, how do we version our databases?

You could put the whole database into version control, I guess.  But that could end up being a lot of data to create a delta for each time.

You could just version the structure.  But then you are left with importing data from your current system, which may or may not work.

So most shops either ignore the problem entirely (not a very good idea, if you ask me), or they require every structural change to be recorded in a SQL script so that by running a series of scripts, you can recreate the database to any point in time.

If you are working on a commercial project, or any other project that has to be deployed to multiple locations, you need to at least create delta SQL scripts so that the new location can get the new data.

And creating these delta scripts isn’t as hard as it seems, once you learn the basics.  But it would be nice if someone created a more elegant solution.

There are several things you might need to change in your database as time passes, but the one you will probably need to do the most is add, remove and modify fields/columns.  You do this with the ALTER TABLE command.

If all you need to do is to add a column to your table, the command is quite simple and looks a lot like the CREATE TABLE statement we looked at last week.

ALTER TABLE tableName
  ADD newField datatype(size,precision) etc...

To remove a field, you use the DROP statement

ALTER TABLE tableName
  DROP newField

To change a field definition

ALTER TABLE tableName
  ALTER COLUMN newField dataType(size,precision) etc...

As far as I know, there is no clean SQL way to change a column name.  You’d have to

1. ADD a new field with the new name and type information
2. Issue an UPDATE command to move the data from one field to the other
3. DROP the old field.

Many times we’d like to ADD several fields, drop several fields and change the data type information all at one time.  This is where most of the documentation you can find online for the ALTER TABLE command falls short.

To Add multiple columns:

ALTER TABLE tableName
  ADD newField datatype(size,precision) etc... ,
  newField2 datatype(size,precision) etc...,  etc...

To drop multiple columns:

ALTER TABLE tableName
  DROP newField,
  newField2

So you’d think you could change multiple columns the same way, right?

WRONG!

To alter multiple columns, you will need to issue multiple ALTER TABLE statements.

You also cannot ADD and DROP in the same ALTER statement.  You will need one ALTER TABLE statement to ADD new columns and another ALTER TABLE statement to DROP all of your columns and yet another ALTER TABLE statement for each column you want to modify.

This isn’t the only reason you wonder what the SQL guys were smoking when they created this syntax.  But I’m sure there was a good reason for this at the time.  Any SQL guys want to enlighten us?

Other Related Items:

Classic Columns Solar White 8.5 x 11 With WindowsClassic Columns Solar White 8.5" x 11" 80lb Covers With Windows - 50 SetsThese wonderful Classic Columns Covers are embossed with a wonderful vertical... Read More >

78 8846 Northstar 4.5" Talking Reflector Telescope900 X 4.5" Motorized Reflector Telescope Real Voice Output (tm) (rvo) Describes The Night Sky In A Human Voice 1.25" Eyepiece Power-boosting Barlo... Read More >

Lightning Vortex

Related Post

• SQL SELECT CASE Instead of IIF
• SQL – Filtering WHERE condition on two rows
• Random in SQL – SQL For Programmers
• SQL CURSOR Performance – SQL For Programmers
• SQL CURSOR – SQL For Programmers

Bookmark to:

Hide Sites

One Response to “SQL For Programmers – ALTERing the TABLE”