ALTER and UPDATE MS Sql table simutaneously

Question

I am trying to alter and update a table at the same time in a MS Sql database using Talend Open Studio for Data Integration. For that I am using a tMssqlRow component which allows multiple updates at a time. Following is the query I am using:

ALTER TABLE Test
ADD col1 int,col2 varchar(10)

UPDATE  Test set col1 = 23, col2 = 'MaxX';

But its not working. When I tried executing this query in MS Sql console, its giving me below error:

Msg 207, Level 16, State 1, Line 5

Invalid column name 'col1'. Msg 207,

Level 16, State 1, Line 5

Invalid column name 'col2'.

Can someone suggest how to resolve this?

Answer 1

First of all, you can’t perform this in one statement. Following are two ways by which you can achieve the required output by using EXEC to run it in a child batch:

ALTER TABLE Test
 ADD col1 INT, col2 VARCHAR(10);

EXEC(“
UPDATE Test
SET    col1 = 23,
      col2 = “MaxX”;
   “);

OR

ALTER TABLE Test

ADD col1 INT NULL CONSTRAINT DF_Test_col1 DEFAULT 23 WITH VALUES, col2 VARCHAR(10) CONSTRAINT DF_Test_col2 NULL DEFAULT 'MaxX' WITH VALUES;