I have a SQLExecutor that inserts data child records for contract IDs. It does fine if it’s a one-to-one but if it’s one contract ID with many children, it only inserts one. Nowhere in my workbench does it group by the contract ID though. If there are 2 contracts and each has 2 children, there are 4 individual rows of data to insert.
I managed to resolve the issue by adding a select statement after the insert to produce a result. I don’t understand why it’s behaving this way though. Note that I’m using a SQL Executor instead of a writer because of the need to set the audit user for each insert.
Below is the sql statement I’m using. Why does the select statement at the end make it work?
FME_SQL_DELIMITER |
EXEC sp_set_session_context 'AuditUser', 'SNOW Sync' |
INSERT INTO CONTRACT_ASSOCIATE (CONTRACT_ASSOCIATE_ID, BA_ID, CONTRACT_ID, CONTRACT_ROLE_ID)
SELECT @Value(CA_ID), '@Value(BA_ID)', @Value(CONTRACT_ID), @Value(CA_ROLE)
WHERE NOT EXISTS (SELECT 1 FROM CONTRACT_ASSOCIATE WHERE CONTRACT_ASSOCIATE_ID = @Value(CA_ID))|
SELECT 'INSERTED' AS ADDED_EA|