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
73 views

1 answer to this question.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.
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,320 points
43 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
46 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
324 views
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer

Define a SQL query? What is the difference between SELECT and UPDATE Query? How do you use SQL in SAS?

Structured query language (SQL) is a programming ...READ MORE

answered Aug 24, 2018 in Data Analytics by ANMOL
• 3,620 points
33 views

© 2018 Brain4ce Education Solutions Pvt. Ltd. All rights Reserved.
"PMP®","PMI®", "PMI-ACP®" and "PMBOK®" are registered marks of the Project Management Institute, Inc. MongoDB®, Mongo and the leaf logo are the registered trademarks of MongoDB, Inc.