ALTER and UPDATE MS Sql table simutaneously

0 votes

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?

Apr 14, 2018 in Talend by anto.trigg4
• 3,420 points
134 views

1 answer to this question.

0 votes

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;

answered Apr 14, 2018 by code.reaper12
• 3,450 points

Related Questions In Talend

0 votes
1 answer
0 votes
1 answer

Comparing 2 files and keeping the unique entries

Hi, To get the unique records from the ...READ MORE

answered Apr 20, 2018 in Talend by geek.erkami
• 2,640 points
123 views
0 votes
1 answer

Looping through all the schemas in Talend

It is really simple to make tOracleInput ...READ MORE

answered Apr 10, 2018 in Talend by code.reaper12
• 3,450 points
127 views
0 votes
1 answer

Parsing JSON in Talend

While working with JSON files, one thing ...READ MORE

answered Apr 2, 2018 in Talend by code.reaper12
• 3,450 points
564 views
0 votes
1 answer
0 votes
1 answer