ID Number: Q64176
1.10 4.20
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.
The simplest method is option 1 described above. The following is an
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 reference words: 1.10 4.20 Transact-SQL