• EN
Question

SQLExecutor Not Inserting All Rows

  • 8 March 2024
  • 4 replies
  • 45 views
rebeccagiannuzz
Rank
Badge +2

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|

4 replies

ctredinnick
Rank
Userlevel 3
Badge +16

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.

rebeccagiannuzz
Rank
Badge +2

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. 

rebeccagiannuzz
Rank
Badge +2

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. 

evieatsafe
Rank
Userlevel 1
Badge +15

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.

Happy to help, Evie

Reply


Community Stats

28,868
Posts
109,990
Replies
36,679
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.