SQL For Programmers – ALTERing the TABLE

misc_vol3_041 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?

Related Post

  • Temporary Tables – SQL For ProgrammersTemporary Tables – SQL For Programmers You may think that you don't need a temporary table.  But if you've ever retrieved data from your database or retrieved data from a table and put it in a list of some sort simply to process it fur...
  • SQL for .NET Programmers–The BookSQL for .NET Programmers–The Book Today, I finally announce the availability of my new book.  SQL For .NET Programmers. This book is based on the series of post I wrote in 2008.  If those post have been valuable to you and y...
  • SQL CURSOR – SQL For ProgrammersSQL CURSOR – SQL For Programmers One of the things it took me a while to figure out was how to get a stored procedure to loop through data in a table and pull out specific data I needed. There are actually two parts to this qu...
  • SQL SELECT CASE Instead of IIFSQL SELECT CASE Instead of IIF I’ve had to do this a couple of times and I just realized I haven’t written about it anywhere. If you need to SELECT a field from a row that returns different content based on the content of th...
  • Basic SQL Commands – SQL For ProgrammersBasic SQL Commands – SQL For Programmers Before we get into the specifics of the commands that can be used within a stored procedure, I think it would be helpful to review some of the more basic commands that we can use that don't really ...