How can I update a surrogate key with MSSQL in Talend, by using ELT components?

[HSHalvorsen]

Hi,

as there is no ELT component for MSSQL to update a surrogate key with SCD, I need to find another solution for updating this key. Already, I insert a surrogate key by using a 'INT IDENTIFY' column in the database. My question is: How can I update a surrogate key with MSSQL in Talend, by using ELT components? My method below might be the wrong approach..

The pictures show what I have tried. I insert a surrogate key in table2 (the column is not included, as it is inserted automatically in the database as another column). The problem is to update this key. I try to update the SK in tELTMSSqlOutput_2, but this error occurs:

Inserting with : 

INSERT INTO EDW.Dimension_Bilagstype(bilagstype_kode,bilagstype_tekst,etl_modified_date,etl_job_name) (SELECT ODS.IBS_Bilagstype.bilagstype, ODS.IBS_Bilagstype.beskrivelse, getdate(), 'job_EDW_Dimension_Bilagstype' FROM ODS.IBS_Bilagstype LEFT OUTER JOIN EDW.Dimension_Bilagstype EDW ON( EDW.bilagstype_kode = ODS.IBS_Bilagstype.bilagstype ) WHERE EDW.sk_bilagstype is null)

--> 28 rows inserted. 

Updating with : 

UPDATE EDW.Dimension_Bilagstype SET bilagstype_kode=(SELECT ODS.IBS_Bilagstype.bilagstype FROM ODS.IBS_Bilagstype LEFT OUTER JOIN EDW.Dimension_Bilagstype EDW ON( EDW.bilagstype_kode = ODS.IBS_Bilagstype.bilagstype )),bilagstype_tekst=(SELECT ODS.IBS_Bilagstype.beskrivelse FROM ODS.IBS_Bilagstype LEFT OUTER JOIN EDW.Dimension_Bilagstype EDW ON( EDW.bilagstype_kode = ODS.IBS_Bilagstype.bilagstype )),etl_modified_date=(SELECT getdate() FROM ODS.IBS_Bilagstype LEFT OUTER JOIN EDW.Dimension_Bilagstype EDW ON( EDW.bilagstype_kode = ODS.IBS_Bilagstype.bilagstype )),etl_job_name=(SELECT 'job_EDW_Dimension_Bilagstype' FROM ODS.IBS_Bilagstype LEFT OUTER JOIN EDW.Dimension_Bilagstype EDW ON( EDW.bilagstype_kode = ODS.IBS_Bilagstype.bilagstype )) 

[FATAL]: talend_okonomi.job_edw_dimension_bilagstype_0_1.job_EDW_Dimension_Bilagstype - tELTMSSqlOutput_2 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:262)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:602)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:524)

at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7418)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3274)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:247)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:222)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:473)

at talend_okonomi.job_edw_dimension_bilagstype_0_1.job_EDW_Dimension_Bilagstype.tELTMSSqlMap_1Process(job_EDW_Dimension_Bilagstype.java:12996)

at talend_okonomi.job_edw_dimension_bilagstype_0_1.job_EDW_Dimension_Bilagstype.runJobInTOS(job_EDW_Dimension_Bilagstype.java:17659)

at talend_okonomi.job_edw_dimension_bilagstype_0_1.job_EDW_Dimension_Bilagstype.main(job_EDW_Dimension_Bilagstype.java:16965)

[statistics] disconnected