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
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));
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.