Using "update or insert" failing with composite primary key
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
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
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.
