Help with Primary Keys and Incremental Load in Table Inserts

Hi,

in my opinion Table Inserts should respect PK checks. If that does not work that may be a bug. You can use Related Record inserts to add an Exists or Not Exists constraint on the insert. So if you are doing a sort of incremental load where you pull a delta from a source and have a large (historical) table as well, you can map the delta to the target table and use a Not Exists (use an equals on the key fields) to insert any records with keys from the historical table that do not exist in the target table.

If you use PK checking, you are also inserting the errors into error tables. The additional I/O for that might be more costly than the Related Records route.

Hi ​@leo.noharet 

This is actually a known bug. We had a ticket in zendesk about this.

It will be fixed in a future release.

Thank you for your quick reply ​@Thomas Lind, do you have any estimate for when this patch is planned to be released? 

​@leo.noharet, I stumbled upon the same problem (with the duplicate key values). The problem seems to be that the code TimeXtender should generate to do a duplicates check for the PKs is simply not generated. The checks for null values are generated as expected. 

So in short, currently when you fill a table with a table insert, the check for nulls is done, but the check for duplicates isn't. 

I heard from colleagues that if you add a mapping to the table it will enforce the PK check, might be worth trying out if that is a workaround until it is fixed.