This statement:
INSERT INTO deleteme
SELECT #t.id, 'test' FROM #t
LEFT JOIN deleteme ON deleteme.id = #t.id
WHERE deleteme.id IS NULL;
...will fail with a primary key violation in the concurrent scenario (i.e. when inserting the same key that is absent in deleteme from several threads at the same time) with the default transaction isolation level of READ COMMITTED
:
Error: Violation of PRIMARY KEY constraint 'PK_DeleteMe'. Cannot insert duplicate key in object 'dbo.deleteme'.
What is the best way to prevent that?
The table looks something like this:
CREATE TABLE [dbo].[DeleteMe](
[id] [uniqueidentifier] NOT NULL,
[Value] [varchar](max) NULL,
CONSTRAINT [PK_DeleteMe] PRIMARY KEY ([id] ASC));
Update
From comments:
Why do you have multiple sessions, which can obviously pull the same id from somewhere, using the same permanent table without having some kind of session key to tell them apart? Even without a duplicate error, how are you going to know which rows belong to which session?
This is a stored procedure that is called by an external service that populates data in this table. The service generates the record id and it provides no guarantee that it won't send the same data twice. Or send the same data at the same time.
The database code is supposed to discard all the records with the same id, if one already exists. The service never sends two records with the same id in the same batch.
#t
must have duplicates at least across sessions. If you have two sessions that are both trying to insert the same primary key value at the same time, one of them will have to get an error. If you want to prevent the error, you'd have to prevent two sessions from trying to insert the same primary key value at the same time. In this case, that would mean ensuring that whatever populates#t
populates a different set of rows in each session. – Justin Cave Aug 20 '15 at 22:13