Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-11-12 : 13:48:00
|
| Raj writes "We have a column defined in one of the tables as a text datatype.We have just realized that it really needs to be of a varchar(16) datatype and there is no need for it toremain text datatype. We are on SQL7.0SP3 / NT4.0SP6.None of the existing data for that text column exceeds 16 bytes.Is there any way to change a column datatype fromtext to varchar.There are about 200,000 rows in the table.I appreciate your help,Thanks,Raj" |
|
|
Apollois
Starting Member
49 Posts |
Posted - 2002-05-10 : 15:02:00
|
quote:
I don't think you can just change it from text to varchar.
I didn't have any problem changing a "text" column to a "varchar(3000)" column in MSSQL 7.0 with MSSQL 2000 Enterprise Manager.All data was converted with no problems. |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-05-10 : 15:18:26
|
quote:
I didn't have any problem changing a "text" column to a "varchar(3000)" column in MSSQL 7.0 with MSSQL 2000 Enterprise Manager.
Yes, it's amazing the magic that Enterprise Manager will do for you behind the scenes. If you'd like to see what the real SQL that was executed looks like, recreate your test scenario, and then instead of clicking the Save button, cleck the Save Changes Script instead. Then you'll see what the system does.I'll bet large sums of money that what you'll see in the script that EM creates is1) Create a new Table with the varchar field instead of Text.2) Insert Into new table rows from old, probably using the Substring syntax that Rob uses.3) Drop Old Table4) Rename new table to Old Table Name.Ta-da! If you had any foreign keys or indices, then it will include the SQL syntax to drop and recreate those as well. EM really doesn't do anything magical. |
|
|
|
|
|
|
Changing a column datatype from text to varchar