Delete Statements for Custom Tables

DataSync's default extraction template does not include a DELETE statement for tables added manually to the incremental load extraction. Without one, records deleted in Sage Intacct stay in the destination table instead of being removed to match.

The DELETE statement for a new table is added to the post execution script of the Audit History extraction. Each statement uses three values specific to the table it targets: the table name, the Audit History object type, and the key field that corresponds to ZZ_Audithistory.Objectkey. The existing statements in the script follow this same pattern and can be used as a reference.

IF OBJECT_ID('##DestinationSchema.<TableName>', 'U') IS NOT NULL
    DELETE t
    FROM ##DestinationSchema.<TableName> t
    WHERE EXISTS (
        SELECT 1
        FROM ##DestinationSchema.ZZ_Audithistory h
        WHERE LOWER(h.Objecttype) = '<objecttype>'
          AND h.Objectkey = CAST(t.<KeyField> AS varchar(100))
    );

Replace <TableName>, <objecttype>, and <KeyField> with the values for the added table, keeping the rest of the statement identical to the existing entries.