topic Re: Error while Full Load (Cannot Insert Duplicate Key row in Object) in Qlik Replicate

Error while Full Load (Cannot Insert Duplicate Key row in Object)

edward221096 — Mon, 25 Apr 2022 03:21:31 GMT

Hi!

We are encountering an error while doing a full load in one of our task (See attached screenshots)

"Cannot insert duplicate key row in object <SchemaName.TableName> with Unique index <Fieldname> the duplicate key value is (xxxx, xxxxxxxxx)"

We would like to know what should be the possible cause here and how can we resolve it?

Re: Error while Full Load (Cannot Insert Duplicate Key row in Object)

OritA — Mon, 25 Apr 2022 06:49:36 GMT

Hi,

You have not mentioned what are the endpoints that are involved in your task. However please refer to the following article as a start point:
https://community.qlik.com/t5/Knowledge/Duplicate-Data-Troubleshooting-Guide/ta-p/1691698
If this does not help please open a case and send us also the task diagnostic package.

Regards,

Orit

Re: Error while Full Load (Cannot Insert Duplicate Key row in Object)

edward221096 — Mon, 25 Apr 2022 06:51:56 GMT

Hi,

It is IBM DB2 for iSeries (AS400) to Azure SQL DB

Thanks,

Edward

Re: Error while Full Load (Cannot Insert Duplicate Key row in Object)

Heinvandenheuvel — Mon, 25 Apr 2022 13:07:46 GMT

First line in the article @OritA references, reads:

Some source database endpoints do not enforce uniqueness and you may be moving data to a target which enforces uniqueness."

Since you mention Azure SQL DB as target DB, that's probably the reason although the wording is somewhat sloppy. For DB2 and Oracle and many more DB's primary keys are just raw data, case sensitive. The SQL Server family tries to 'help' simpleton users and defaults to case insensitive Primary keys. That makes "XX" a duplicate to "Xx". You may need to re-create the target table with PK attributes:

COLLATE Latin1_General_CS_AS

This also means you cannot allow Replicate to drop and re-created, but must use Truncate for reload.

Alternatively you may decide that differences in case in PK are in fact inconsistent source data and choose to fix that.

Many topics on the interwebs discuss this. See for example: https://www.sqlines.com/oracle/case_sensitivity_primary_key

Hein.

Re: Error while Full Load (Cannot Insert Duplicate Key row in Object)

Bill_Steinagle — Fri, 29 Jul 2022 18:26:46 GMT

Hello,

Thank you for the post to the QDI Forums. Since you are working with iSeries Source and there is no PK/Index you can always use the option to add the RRN as the PK when one it not defined on the Source for the Table and uniqueness. Check the Source Endpoint and the Advanced Tab for this RRN option. Keep in mind this can be used as long as the RRN on the iSeries Source are not reused as this will cause the same issue with the Duplicate PK.

Thanks!

Bill