Sql Server 2005 - Insert if not exists
exists, insert, sql-server, transactions
Solution
With TRY/CATCH you can avoid the extra read
BEGIN TRY
INSERT etc
END TRY
BEGIN CATCH
IF ERROR_NUMBER() <> 2627
RAISERROR etc
END CATCH
- A NOT EXISTS will read the table, whether in the IF or WHERE
- The INSERT requires a read to check uniqueness
If you can discard duplicates, this is a highly scalable technique
Links:
- See my answers here: Only inserting a row if it's not already there and SQL Server 2008: INSERT if not exits, maintain unique column
- If you requires UPDATEs too: Which is the best choice in delete-insert vs if-update else-insert?
Problem
There is lots of information in the internet regarding this common "problem". Solutions like: ``` IF NOT EXISTS() BEGIN INSERT INTO (...) END ``` are not thread-safe in my opinion and you will probably agree. However could you confirm that putting the exist into the where clause of one single select would solve the problem of the highest concurrency in sql engine? Is it enough? ``` insert into Table (columns) select column1, column2, column3 where not exists (select top 1 1 from Table where something) ``` Should be there also added some higher transaction level or can this be executed on a default one: committed? Would this work under uncommitted level? Thanks! //Added later Can i assume that both sql' are correct: 1) set transaction isolation level repeatable read ``` IF NOT EXISTS() BEGIN INSERT INTO (...) END ``` 2) set transaction isolation level repeatable read ``` insert into Table (columns) select column1, column2, column3 where not exists (select top 1 1 from Table where something) ```