MS SQL Server Merge using ELT

chamak — Tue, 11 Dec 2018 01:12:28 GMT

I am trying to achieve

tELTMSSQLInput -> tELTMSSQLMap -> tELTMSSQLOutput

and trying to use merge statement like in tELTOracleOutput

Any help on that? The update statement is completely wrong in tELTMSSQLOutput.

Re: MS SQL Server Merge using ELT

Anonymous — Tue, 11 Dec 2018 07:12:19 GMT

Hello,

What's problem are you getting? What does your where clause look like?

Could you please have a look at this related topic and let us know if it helps?

https://community.talend.com/t5/Design-and-Development/tELTORACLE-UPDATE/m-p/124292

Best regards

Sabrina

Re: MS SQL Server Merge using ELT

chamak — Tue, 11 Dec 2018 23:52:09 GMT

Hi @xdshi

Sorry for my quick non-informative question. Let me fix that.

What I'm trying to achieve is a very straight forward update all of TGT table from matching SRC table, which looks something like this:

The configuration inside the tELTMSSqlMap component looks like this:

When I run the job, it's creating a query like this:

Starting job testELTUpdate at 10:40 12/12/2018.
Updating with : 
UPDATE ETL.TGT SET PK=(SELECT SRC.PK  FROM  ETL.SRC SRC),STD_NAME=(SELECT  SRC.STD_NAME  FROM  ETL.SRC SRC),STD_NUM=(SELECT  SRC.STD_NUM  FROM  ETL.SRC SRC)   WHERE 1=1

Exception in component tELTMSSqlOutput_1 (testELTUpdate)
com.microsoft.sqlserver.jdbc.SQLServerException: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:254)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1608)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:578)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:508)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7240)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2869)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:243)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:218)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:461)
	at etl_talend.testeltupdate_0_1.testELTUpdate.tELTMSSqlMap_1Process(testELTUpdate.java:1074)
	at etl_talend.testeltupdate_0_1.testELTUpdate.tDBConnection_2Process(testELTUpdate.java:1430)
	at etl_talend.testeltupdate_0_1.testELTUpdate.runJobInTOS(testELTUpdate.java:6088)
	at etl_talend.testeltupdate_0_1.testELTUpdate.main(testELTUpdate.java:5591)
[FATAL]: etl_talend.testeltupdate_0_1.testELTUpdate - tELTMSSqlOutput_1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
com.microsoft.sqlserver.jdbc.SQLServerException: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:254)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1608)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:578)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:508)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7240)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2869)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:243)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:218)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:461)
	at etl_talend.testeltupdate_0_1.testELTUpdate.tELTMSSqlMap_1Process(testELTUpdate.java:1074)
	at etl_talend.testeltupdate_0_1.testELTUpdate.tDBConnection_2Process(testELTUpdate.java:1430)
	at etl_talend.testeltupdate_0_1.testELTUpdate.runJobInTOS(testELTUpdate.java:6088)
	at etl_talend.testeltupdate_0_1.testELTUpdate.main(testELTUpdate.java:5591)
[statistics] disconnected

Job testELTUpdate ended at 10:40 12/12/2018. [exit code=1]

Which doesn't make any sense to me.

Notice the update query it's creating:

UPDATE ETL.TGT SET PK=(SELECT SRC.PK  FROM  ETL.SRC SRC),STD_NAME=(SELECT  SRC.STD_NAME  FROM  ETL.SRC SRC),STD_NUM=(SELECT  SRC.STD_NUM  FROM  ETL.SRC SRC)   WHERE 1=1

the "1=1" clause is added by me in output component, which is configured for update.

I hope I explained the problem much better. Please advise..

Edit: Using Talend Cloud Data Management Platform (7.0.1)

Re: MS SQL Server Merge using ELT

chamak — Thu, 13 Dec 2018 06:02:41 GMT

Anyone please?

Re: MS SQL Server Merge using ELT

maayandermer — Fri, 13 Mar 2020 05:09:13 GMT

Hi @chamak if you found a solution feel free to share it

topic Re: MS SQL Server Merge using ELT in Talend Studio

MS SQL Server Merge using ELT

Re: MS SQL Server Merge using ELT

Re: MS SQL Server Merge using ELT

Re: MS SQL Server Merge using ELT

Re: MS SQL Server Merge using ELT