Is sql statement "where not exists" atomic? No it's not.
/* Short question: is "where not exists" atomic? Short answer: No it will fail, if not in transaction or using locking hint. Even merge will fail. Follow steps to reproduce below. (Written on sql2008r2) */ /* Preparation script */ /* drop table dbo.Monkey drop table stage.Monkey */ create table dbo . Monkey ( MonkeyId int , MonkeyDate datetime , primary key clustered ( Monkeyid ) ) create table stage . Monkey ( MonkeyId int identity ( 1 , 1 ) , MonkeyDate datetime ) go set nocount on insert stage . Monkey select getdate ( ) go 10 insert stage . Monkey select getdate ( ) from stage ...