Skip to main content
A
Arjun999910-Marble
10-Marble
January 17, 2024
Solved

Skip the service execution to next line when there is an error in the current line

  • January 17, 2024
  • 4 replies
  • 1733 views

I'm trying to execute a batch of SQL insert queries with multiple data values. I've stored the values (to be inserted) in an Infotable, iterating over the infotable and building the INSERT query statements and then making a call to "RunDatabaseCommand" service of a DB thing. 

 

Ideally, with different set of values the said service inserts the values into the DB. 

When one of the multiple values (at the time of iteration) has already exist, the service throws an error (DB error i.e., Primary key violation) - this is expected. However, the service execution stops at the existing entry and doesn't reach the remaining values. 

 

For example - the Infotable values (v1, v2, v3, v4 etc., ) needs to be inserted. These values are iterated, over each iteration the values are being inserted into the DB. Suppose "v2" values already exist in the DB and v3 and v4 values doesn't exist in the DB. At the time iteration execution with v1 value gets inserted. Execution with v2 values throws an error, then the rest of the values i.e., v3 and v4 never gets executed / inserted into the DB. 

 

Here, when the execution reaches v2 value, it should throw an error / warn message, and then the execution should continue with v3 and v4 value insertions. How can I achieve this behaviour?? 

 

I've tried putting a TRY / CATCH block around the code which makes a call to RunDatabaseCommand service. But the execution is getting stopped post reaching the existing entry. 

Best answer by Arjun9999

Using Try / Catch Block I was able to handle the error situation, by using the Update DB query 

4 replies

Rocko
Rocko19-Tanzanite
19-Tanzanite
January 17, 2024

Can you post your code? It's unclear if you post one large statement or multiple inserts.

If you have one large statement, it's only one DB transaction and TWX can't interfere with that. You would have to run multiple individual inserts. Those you should be able to try/catch.

A
Arjun999910-MarbleAuthorAnswer
10-Marble
March 12, 2024

Using Try / Catch Block I was able to handle the error situation, by using the Update DB query 

B
Bikash_Panda14-Alexandrite
14-Alexandrite
January 17, 2024

As you already have the data as an infotable, why don't you remove the duplicates before trying to insert to the DB ? That would also prevent unnecessary DB requests.

V
VladimirRosu_11662719-Tanzanite
19-Tanzanite
January 17, 2024

As @Rocko said, it would be good to see your code. I doubt a try/catch would not catch this if you have individual INSERTs.

N
nmilleson17-Peridot
17-Peridot
February 1, 2024

@Arjun9999 ,

 

Is this postgres?  If so, you can use the ON CONFLICT DO NOTHING statement outlined here:
https://www.postgresql.org/docs/current/sql-insert.html

Specifically something like this:

-- This statement could infer a partial unique index on "did"
-- with a predicate of "WHERE is_active", but it could also
-- just use a regular unique constraint on "did"
INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International')
 ON CONFLICT (did) WHERE is_active DO NOTHING;

 

- Nick

Terms & ConditionsCookie settingsAccessibility statement