INF: Dynamically Altering Table Definitions in SQL Server

  - Microsoft SQL Server version 4.2 for OS/2

SUMMARY

Transact-SQL supplies the ALTER TABLE command to add new columns to an existing table. This article explains how to perform the following additional table definition changes:

• Changing the length of a column
• Changing the data type of a column

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.

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)

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

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