INF: Dynamically Altering Table Definitions in SQL Server (64176)

SUMMARY

In SQL Server versions 7.0 and later, the Transact-SQL ALTER TABLE command allows you to modify a table definition by altering, adding or dropping columns and constraints, or by disabling or enabling constraints and triggers. However, in SQL Server versions prior to 7.0, you cannot change the length or data type of an existing column or delete a column. This article explains how these additional changes can be made.

MORE INFORMATION

The above table definition changes can be done in SQL Server in at least three ways:

Use the following Transact-SQL statement to select data from the old table into a newly created table, performing the data type conversion as required:
```
      INSERT <tablename>
      SELECT <col list>
      FROM <table list>

						
```
Use a view to create the illusion of a change in table definition.
Copy the data to an operating system file using BCP. Re-create the table to conform to the new requirements and upload the data, again using BCP.

The simplest method is option 1 described above. The following is an example:

Given the following table definition and data:

    create table test1
     (col1 char(10),
      col2 int)

    insert test1 values("First Row",1)
    insert test1 values("Second Row",2)
    insert test1 values("Third Row",3)

If we wanted to expand col1 to take 15 characters and change the data type of col2 to char, the following would result:

    create table test2
     (col1 char(15),
      col2 char(5))

    insert test2
    select col1, convert(col2,char(5))
    from test1