CODEWITHSUNDEEP
c#mysqlsql
John
John

Reputation: 165

Is it OK to use exceptions to mark when a duplicate SQL insertion is attempted?

Scenario

I have a database that has two EQUAL tables with different name (let’s call them Table1 and Table2). Table1 sends a bunch of data periodically to Table2 through a C# program. Some of this data is already inside Table2 so a simple Exception pops up on the cmd and the program goes on. I don’t want the repeated data, so this is fine and the program works exactly the way I want.

My question

I was wondering if it was ok that the C# query function had to manage so many exceptions like on this case (20% of the inserts are duplicate and head to the exception). Because although the program works, the function maybe inefficient or “harmful” (in some unknown way, for example, causing more delays) for the program itself.

If it does, how can I solve this from a simple “INSERT INTO” query? Is there any command that shall make it ‘the good way to do it’?

Upvotes: 0

Views: 67

Answers (2)

T.S.
T.S.

Reputation: 19350

Since you in MySql, you can make all statements complete with no errors using this syntax

INSERT INTO T1 (a, b)
    SELECT a, b FROM T2
ON DUPLICATE KEY UPDATE b = c;

If you specify an ON DUPLICATE KEY UPDATE clause and a row to be inserted would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row occurs.

Refer this

Upvotes: 3

nostalgk
nostalgk

Reputation: 229

Exceptions are used to define behavior that is otherwise unexpected; "exceptional" behavior. It's easier to think about if you get out of the idea that they are inherently bad, per se, but rather indicate unintended behavior.

With what you have written now, using exceptions thrown when a duplicate record is attempted to be inserted, I would say this is correct usage of an exception. It identifies a behavior that is not implicitly supported by your code.

However, there are alternatives as well. You could, for example, restructure your application to only attempt to insert a record if a duplicate does not exist. I'm not sure what your application looks like, so I'm unsure if you're using a framework that makes this easy to implement or not. User eocron mentioned that a MERGE command might be closer to the functionality you're looking for.

My personal philosophy would be to avoid using an exception if it is common for duplicates to be inserted, and that this behavior should be sanitized instead.

Regardless, I believe it would behoove you to log this exceptional behavior in some way to determine the frequency of it.

Upvotes: 1

Related Questions