CODEWITHSUNDEEP
sqlsql-server
Mark Hurd
Mark Hurd

Reputation: 10931

Ignore Unique Constraint and still insert other rows

I think I want the semantics of both UNIQUE and IGNORE_DUP_KEY.

I have an INSERT query that looks over recent data and inserts a unique key-value pair. It runs often and takes seconds at most.

I have another INSERT query that looks at all data and inserts unique key-value pairs. It takes minutes to run and probably finds nothing to do, except it will sometimes see the same data as the recent query, and will decide to insert the same pair.

I've implemented a UNIQUE constraint, so that's not a problem in itself, but I'd like other records determined by the long-running query to be inserted irrespective of the duplicates.

Both queries do explicitly have a clause similar to

WHERE NOT EXISTS (SELECT Key, Value From TargetTable TT
   WHERE TT.Key = Result.Key AND TT.Value = Result.Value)

Upvotes: 0

Views: 1242

Answers (2)

Mark Hurd
Mark Hurd

Reputation: 10931

I've decided to split the second query into two so that I now have three queries:

  • Quick 15 minute past query running every 30 seconds.
  • Nearly as quick query looking back to midnight UTC; may have duplicate key failures but retrying won't take long; runs every 2 minutes.
  • Slow query reviewing all data, but ignoring the current UTC day; won't have duplicate key failures; runs twice a day.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

If I understand correctly, you want something like MySQL's INSERT IGNORE. I don't believe this functionality exists in SQL Server. Your specific problem appears to be updates on this (or another table) that occur during the updating process, introducing duplicate keys.

One option is to put a lock on the table during this operation, not allowing any other operations. That is probably not feasible given the time frame for the lock.

Another option is to take the long running query and stash the results into a temporary table. Then, do the inserts from this table, one at a time, capturing and ignoring any violations of the unique constraint.

Upvotes: 1

Related Questions