I don’t know specifically. I assume you’re checking the database insert has happened correctly outside of FME. SQLExecutors run once per feature into it, maybe the stored procedure works differently if things aren’t grouped by contract ID. Do you have any null CA_IDs - your script might deny inserts on nulls?

It doesn’t make sense that adding a select statement would change the behaviour of the prior statements, since that’s the point of the | delimiter. All that Select should do is pass features out of the transformer at the end.

Yep, checking the insert outside of FME. Rerunning the same SQLExecutor will actually insert the missing ones sometimes. Tried running the inserts directly in SQL to make sure there wasn’t some little syntax thing and they run just fine. No null CA_IDs. 

No clue why adding a select works. Only reason I figured that out is in a few of the SQLExecutors, I happened to have it running a select for something further down the line and those ones never had issues inserting multiple rows for the same CA_ID. 

Adding to this - adding the select at the end of SQLExecutor only works if I run the workbench manually. As soon as I run it using a .bat file and task scheduler, it’s back to only inserting one row per CA_ID. 

Hi @rebeccagiannuzz thanks for your question! Speculating here, but maybe you could be missing the | after the INSERT part… e.g.

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))|

Would you be able to provide a log? Otherwise, perhaps you could create a support case with us so that we can investigate further. Sorry to not be of more help.