Microsoft KB Archive/64176

= INF: Dynamically Altering Table Definitions in SQL Server =

Article ID: 64176

Article Last Modified on 2/14/2005

-

APPLIES TO


 * Microsoft SQL Server 4.21a Standard Edition
 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q64176



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 SELECT FROM

 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(&quot;First Row&quot;,1) insert test1 values(&quot;Second Row&quot;,2) insert test1 values(&quot;Third Row&quot;,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: kbprogramming KB64176

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.