How can I change a column with datatype "text" to "int"?

I made a mistake when I first created the column and just found out when I
tried to calculate. I tried using the design feature on Enterprise Manager,
but got an error message that this couldn't be done. Any help is deeply
appreciate.Don't use Enterprise Manager for this. Open Query Analyzer and connect to
the correct database.
-- add a new column to the table;
ALTER TABLE tablename ADD temp_column INT;
-- copy the data from the text column;
UPDATE tablename SET temp_column = text_column WHERE ISNUMERIC(text_column)
= 1;
-- drop the text column;
ALTER TABLE tablename DROP COLUMN text_column;
-- rename the new column;
EXEC sp_rename 'tablename.temp_column', 'real_column', 'COLUMN';
Of course, you'll want to put in the correct names for tablename,
text_column, real_column, etc.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Karolus" <Karolus@.discussions.microsoft.com> wrote in message
news:3B1473BD-7C92-4D18-80FF-F0DEE26C408F@.microsoft.com...
>I made a mistake when I first created the column and just found out when I
> tried to calculate. I tried using the design feature on Enterprise
> Manager,
> but got an error message that this couldn't be done. Any help is deeply
> appreciate.|||Thank you, Aaaron. I will use what you provided and make the change. Thank
s
mucho. karolus
"Aaron Bertrand [SQL Server MVP]" wrote:

> Don't use Enterprise Manager for this. Open Query Analyzer and connect to
> the correct database.
> -- add a new column to the table;
> ALTER TABLE tablename ADD temp_column INT;
> -- copy the data from the text column;
> UPDATE tablename SET temp_column = text_column WHERE ISNUMERIC(text_column
)
> = 1;
> -- drop the text column;
> ALTER TABLE tablename DROP COLUMN text_column;
> -- rename the new column;
> EXEC sp_rename 'tablename.temp_column', 'real_column', 'COLUMN';
> Of course, you'll want to put in the correct names for tablename,
> text_column, real_column, etc.
>
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
> "Karolus" <Karolus@.discussions.microsoft.com> wrote in message
> news:3B1473BD-7C92-4D18-80FF-F0DEE26C408F@.microsoft.com...
>
>