topic Re: Using "update or insert" failing with composite primary key in Talend Studio

Using "update or insert" failing with composite primary key

Anonymous — Sat, 16 Nov 2024 13:02:44 GMT

Hello,
I am trying to migrate data from Oracle to MSSQL. This job needs to be re-runnable such that it will update data (by key) in dest database (if already present) and insert if not present. The source oracle table has a composite pk comprising 4 columns. In advanced section, I have set the Update Key to be these four columns, but I see the following errors being reported ar run time:
Violation of PRIMARY KEY constraint 'PK__AUDIT_MV__A9AE970D0AD2A005'. Cannot insert duplicate key in object 'dbo.AUDIT_MVT'.
Violation of PRIMARY KEY constraint 'PK__AUDIT_MV__A9AE970D0AD2A005'. Cannot insert duplicate key in object 'dbo.AUDIT_MVT'.
Violation of PRIMARY KEY constraint 'PK__AUDIT_MV__A9AE970D0AD2A005'. Cannot insert duplicate key in object 'dbo.AUDIT_MVT'.
Violation of PRIMARY KEY constraint 'PK__AUDIT_MV__A9AE970D0AD2A005'. Cannot insert duplicate key in object 'dbo.AUDIT_MVT'.
Violation of PRIMARY KEY constraint 'PK__AUDIT_MV__A9AE970D0AD2A005'. Cannot insert duplicate key in object 'dbo.AUDIT_MVT'.
Violation of PRIMARY KEY constraint 'PK__AUDIT_MV__A9AE970D0AD2A005'. Cannot insert duplicate key in object 'dbo.AUDIT_MVT'.
Violation of PRIMARY KEY constraint 'PK__AUDIT_MV__A9AE970D0AD2A005'. Cannot insert duplicate key in object 'dbo.AUDIT_MVT'.
Violation of PRIMARY KEY constraint 'PK__AUDIT_MV__A9AE970D0AD2A005'. Cannot insert duplicate key in object 'dbo.AUDIT_MVT'.
......
PS. Am using 42.0.M3_r54685

Re: Using "update or insert" failing with composite primary key

Anonymous — Fri, 11 Mar 2011 02:28:36 GMT

Hi
From the error message, we can see there is a primary key PK__AUDIT_MV__A9AE970D0AD2A005 in target table, If you try to update a field in your row that is part of a primary key constraint, even if it has the same value as the row you're trying to update already has, then the command assumes that you're doing an insert.
Best regards
Shong

Re: Using "update or insert" failing with composite primary key

Anonymous — Fri, 11 Mar 2011 09:24:02 GMT

Thanks Shong,
What I am trying to do is match on PK ('PK__AUDIT_MV__A9AE970D0AD2A005') and update the existing dest record (by PK) or insert. Is there a way to do this be just using a tOracleInput sending to a tMSSqlOutput ?
Any suggestions would be much appreciated.