If someone asks how to perform an
INSERT-IF-NOT-EXISTS operation in SQL Server, they'll typically get an answer like this back:
IF NOT EXISTS(SELECT 1 FROM [TheTable] WHERE [ColumnX] = @valX) INSERT [TheTable] ([ColumnX]) VALUES (@valX)
The problem I'm seeing with this is that in between the
SELECT statement and the
INSERT statement, the situation could change externally. Another process could insert the
ColumnX value after the
SELECT statement, but before the
INSERT statement, resulting in an error being raised.
I've worked in software for a while, but am not a DB specialist, and when I search for an answer to this problem in SQL Server, the results I'm seeing are either irrelevant or quite difficult to really apply (because they're either answering a different question or are written in terms tailored to DB specialists).
So in layman's terms, how do you resolve this problem? I did get a little bit rusty with SQL in recent history, but am thinking that there really should be a pragmatic locking mechanism to use for this (whether there is or isn't). As a fallback, maybe error handling can specifically determine whether an error raised matches this exact issue, ignoring it in that specific case.
Preferably this doesn't involve just locking the whole table every time.