INF: Dynamically Altering Table Definitions in SQL Server

ID: Q64176

The information in this article applies to:

Microsoft SQL Server for OS/2, version 4.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.

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

Additional query words: Transact-SQL


Keywords          : kbprg SSrvServer 
Version           : 4.2
Platform          : OS/2 
Issue type        :

Last Reviewed: March 9, 1999