Ignore insert activity if records are alredy in DB

[god_prophet]

I have 2 workflows: One works as I expect (Workflow B) the other doesn't (Workflow A). 

Workflow A, Insert Activity display logs ends with: 10/14/2024 6:59:44 PM WFE-550001 *** Task interrupted '13730386:150' (activity 'writer') *** 

Whereas Workflow B, Insert Activity display logs ends with: 10/14/2024 5:38:18 PM [28/28] record(s) processed (step 'Insert TipDif N')
10/14/2024 5:38:18 PM RE iRecProcState>:#(1)# AND iRecProcState<=:#(2)# AND COALESCE(sPKey_1,NULL) IS NULL AND COALESCE(sPKey_2,NULL) IS NULL' could not be executed. Param(0)=27 Param(1)=28

=============================================================

Workflow A: when it arrives to the "Insert new records" and the records are already in the DB, the activity enter an error state, even whe in the Advance tab, I've put ignore.

 

Display logs say: 10/14/2024 6:59:44 PM WFE-550001 *** Task interrupted '13730386:150' (activity 'writer') ***
10/14/2024 6:59:44 PM sPKey_1,NULL) IS NULL AND COALESCE(sPKey_2,NULL) IS NULL' could not be executed. Param(0)=100 Param(1)=101
10/14/2024 6:59:44 PM L),COALESCE( tsFld2773483547, NULL),COALESCE( tsFld2774335516, NULL),COALESCE( dFld2775384093, 0),COALESCE( sFld2783641630, NULL),COALESCE( sFld2784755743, NULL) FROM wkf13730386_150_1 WHERE iRecProcState>:#(1)# AND iRecProcState<=:#(2)# AND COALESCE(
10/14/2024 6:59:44 PM TIPCONSUMOCRM,sTIPDIFERENCIAL) SELECT COALESCE( iFld2764374035, 0),COALESCE( iFld2766012436, 0),COALESCE( sFld2767257621, NULL),COALESCE( sFld2769813527, NULL),COALESCE( sFld2770468888, NULL),COALESCE( sFld2771255321, NULL),COALESCE( tsFld2772434970, NUL
10/14/2024 6:59:44 PM WDB-200001 SQL statement 'INSERT INTO BcpDTI_TARJETACREDITOCONSUMOCRM(iCTDDIATRXU1M,iCTDTRXTARJETACREDITOU1M,sCODCLAVECTA,sCODCLAVETARJETA,sCODINTERNOCOMPUTACIONAL,sDESTIPCONSUMOCRM,tsFECACTUALIZACIONTABLA,tsFECDIA,tsFECULTCONSUMO,iMTOCONSUMOACUMDIASOL,s
10/14/2024 6:59:44 PM 1e520b9f8721b2a28fffb7eab25ec03a1072, DE) already exists. .

Workflow B: when it arrives to the "Insert new records" and the records are already in the DB, it goes to the "Already in DB" in ramification.

Display logs say: 

10/14/2024 5:38:18 PM [28/28] record(s) processed (step 'Insert TipDif N')
10/14/2024 5:38:18 PM RE iRecProcState>:#(1)# AND iRecProcState<=:#(2)# AND COALESCE(sPKey_1,NULL) IS NULL AND COALESCE(sPKey_2,NULL) IS NULL' could not be executed. Param(0)=27 Param(1)=28
10/14/2024 5:38:18 PM COALESCE( sFld1791492120, NULL),COALESCE( sFld1792278553, NULL),COALESCE( sFld1793327130, NULL),COALESCE( tsFld1795162139, NULL),COALESCE( tsFld1796800540, NULL),COALESCE( sFld1797849117, NULL),COALESCE( sFld1798897694, NULL) FROM wkf13718980_9716_1 WHE
10/14/2024 5:38:18 PM ALESCE( sFld1784152080, NULL),COALESCE( sFld1785397265, NULL),COALESCE( sFld1786249234, NULL),COALESCE( sFld1787101203, NULL),COALESCE( sFld1788084244, NULL),COALESCE( sFld1788870677, NULL),COALESCE( sFld1789788182, NULL),COALESCE( sFld1790705687, NULL),
10/14/2024 5:38:18 PM ESCE( sFld1773993992, NULL),COALESCE( sFld1774911497, NULL),COALESCE( sFld1776156682, NULL),COALESCE( sFld1777074187, NULL),COALESCE( sFld1777991692, NULL),COALESCE( sFld1779630093, NULL),COALESCE( sFld1780678670, NULL),COALESCE( sFld1781530639, NULL),CO
10/14/2024 5:38:18 PM ESCAMPOVARIABLE19,sDESCAMPOVARIABLE20,tsFECACTUALIZACIONTABLA,tsFECDIA,sTIPDIFERENCIAL,sTIPGRUPOCOMUNICACIONVENTA) SELECT COALESCE( sFld1770258436, NULL),COALESCE( sFld1771503621, NULL),COALESCE( sFld1772224518, NULL),COALESCE( sFld1773076487, NULL),COAL
10/14/2024 5:38:18 PM CAMPOVARIABLE6,sDESCAMPOVARIABLE7,sDESCAMPOVARIABLE8,sDESCAMPOVARIABLE9,sDESCAMPOVARIABLE10,sDESCAMPOVARIABLE11,sDESCAMPOVARIABLE12,sDESCAMPOVARIABLE13,sDESCAMPOVARIABLE14,sDESCAMPOVARIABLE15,sDESCAMPOVARIABLE16,sDESCAMPOVARIABLE17,sDESCAMPOVARIABLE18,sD
10/14/2024 5:38:18 PM WDB-200001 SQL statement 'INSERT INTO BcpDTI_COMUNICACIONMASIVAEXCEPCIONALCLIENTE(sCODCOMUNICACIONMASIVAEXCEPCIONAL,sCODINTERNOCOMPUTACIONAL,sCODMATRICULA,sDESCAMPOVARIABLE1,sDESCAMPOVARIABLE2,sDESCAMPOVARIABLE3,sDESCAMPOVARIABLE4,sDESCAMPOVARIABLE5,sDES
10/14/2024 5:38:18 PM PGS-220000 PostgreSQL error: ERROR: duplicate key value violates unique constraint "excepcionalcliente_id_8bfac11e" DETAIL: Key (scodcomunicacionmasivaexcepcional, scodinternocomputacional)=(3454445, 13139895) already exists. .

[ParthaSarathy]

Hi @god_prophet ,

You can select operation as 'insert or update' and in record identification select 'using reconciliation key' and add your schema's primary and map with temp tables field. So when the records already exist in the database then the data will get updated or if the data is new then it will get inserted.

The other way is, refer to this document - Here after split activity, connect the 'primary key not equal to 0' transition to end activity. So that, only the new records will be directed to update data activity.

[god_prophet]

Hi @ParthaSarathy , thank you, but this doesn't answer the question:

1: I need to have new records separated, so for this I use the "insert" option, instead of "insert or update".
2.- There is other branch of the split that takes care of the modified records, and here I use the "update" option of the activity.

The question is why in Workflow B the "already in data base records" get ignored, but not in workflow A (here the update activity flashes in red).