Column rename without using sp_rename

Hello,
I just need to rename a column in a database (for the moment I'm not
sure the table has data or not). The statements I need to store it in a
text file (a.sql) that is read and executed through an OleDB Connection.
I tried something like this :
alter table "table1" add "column2" char(20);
UPDATE "table1" set "column2" = "column1";
alter table "table1" drop column "column1";
, but it raises and error, like "column2" is a an undefined column.
Is there a way to acomplish what I want?
Regards,
D.M.Daniel,
You need to run the UPDATE in a separate batch so that SQL Server will
already be aware of the existence of the new column when it resolves the
batch:
alter table "table1" add "column2" char(20);
GO
UPDATE "table1" set "column2" = "column1";
alter table "table1" drop column "column1";
BG, SQL Server MVP
www.SolidQualityLearning.com
"Daniel Mihaita" <fishx@.hotmail.com> wrote in message
news:%23yoLsDsAFHA.3820@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I just need to rename a column in a database (for the moment I'm not
> sure the table has data or not). The statements I need to store it in a
> text file (a.sql) that is read and executed through an OleDB Connection.
> I tried something like this :
> alter table "table1" add "column2" char(20);
> UPDATE "table1" set "column2" = "column1";
> alter table "table1" drop column "column1";
> , but it raises and error, like "column2" is a an undefined column.
> Is there a way to acomplish what I want?
> Regards,
> D.M.|||sp_rename is a more efficient way to rename a column. Why don't you
want to use that?
To use your method you will have to execute the ALTER and UPDATE
statements as separate batches, otherwise SQL can't compile the script
without resolving the non-existent column name.
--
David Portas
SQL Server MVP
--|||Problem is that the batch is parsed at the same time, and at t5hat stage, th
e column haven't been
added (that command haven't been executed). This is what confuses SQL Server
when you later refer to
the column name. Put the ALTER and UPDATE in different batches.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Daniel Mihaita" <fishx@.hotmail.com> wrote in message
news:%23yoLsDsAFHA.3820@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I just need to rename a column in a database (for the moment I'm not sur
e the table has data or
> not). The statements I need to store it in a text file (a.sql) that is rea
d and executed through
> an OleDB Connection.
> I tried something like this :
> alter table "table1" add "column2" char(20);
> UPDATE "table1" set "column2" = "column1";
> alter table "table1" drop column "column1";
> , but it raises and error, like "column2" is a an undefined column.
> Is there a way to acomplish what I want?
> Regards,
> D.M.|||sp_rename is a more efficient way to rename a column. Why don't you
want to use that?
To use your method you will have to execute the ALTER and UPDATE
statements as separate batches, otherwise SQL can't compile the script
without resolving the non-existent column name.
--
David Portas
SQL Server MVP
--|||I need to keep the SQL statements working also for ASA. (There is
ALTER TABLE "table1" RENAME ...). I tried to use some standard SQL to
rename the column ...
Daniel Mihaita wrote:
> Hello,
> I just need to rename a column in a database (for the moment I'm not
> sure the table has data or not). The statements I need to store it in a
> text file (a.sql) that is read and executed through an OleDB Connection.
> I tried something like this :
> alter table "table1" add "column2" char(20);
> UPDATE "table1" set "column2" = "column1";
> alter table "table1" drop column "column1";
> , but it raises and error, like "column2" is a an undefined column.
> Is there a way to acomplish what I want?
> Regards,
> D.M.