T-SQL Update else Insert best approach

Question

Possible Duplicate:
Atomic UPSERT in SQL Server 2005

I am using the following construct to insert a new record if it does not exists. If it exists then it will update that record. I am wondering that if it is thread safe. I mean two threads try to insert record which will create duplicate entries. What is the best approach to handle such type of queries? Do I need to put these statements in a transaction block?

UPDATE Table1 SET (...) WHERE Column1='SomeValue'

IF @@ROWCOUNT=0
    INSERT INTO Table1 VALUES (...)

Andomar · Answer 1 · 2011-07-04T17:23:33.723

4

It's a bit safer if you do the insert first:

insert Table1 (...columns...) select ..values... where not exists (
    select * from table1 where Column1 = 'SomeValue')

if @@rowcount = 0 update Table1 set (...) where Column1 = 'SomeValue'

That way, the check for existance and insert are part of the same statement. So there is no room between the update and the insert where another connection could insert the row.

edited Jul 04 '11 at 17:23

answered Jul 04 '11 at 13:43

Andomar

232,371
49
380
404

Incorrect syntax of Insert statment. SQL Server does not like it. I think I can use serializable lock. – kumar Jul 04 '11 at 14:51
@Andomar: `WHERE` is not essentially part of `INSERT`, therefore it cannot be used with `INSERT...VALUES`. But it *can* be part of `SELECT`, which means you can use `WHERE` with `INSERT...SELECT`. – Andriy M Jul 04 '11 at 15:53
@kumar, Andriy M: You're right, edited in answer – Andomar Jul 04 '11 at 17:24

t-clausen.dk · Accepted Answer · 2011-07-04T16:11:58.850

This is a more detailed explanation of Andomar's post. I am giving the credit to Andomar, just illustrating how it could be more detailed and corrected a small bug.

DECLARE @s VARCHAR(100)
SET @s = 'somevalue' 

DECLARE @t TABLE (column1 VARCHAR(100), column2 VARCHAR(18))

INSERT @t 
SELECT @s, 'First example'
WHERE NOT EXISTS ( SELECT 1 FROM @t WHERE Column1 = @s) 
IF @@rowcount = 0 UPDATE @t SET column2 = 'Second example' WHERE Column1 = @s

-- at first the value does not exists and is inserted
SELECT * FROM @t

-- same script again, notice how the second example is chosen because it already exists
INSERT @t 
SELECT @s, 'First example'
WHERE NOT EXISTS ( SELECT 1 FROM @t WHERE Column1 = @s) 
IF @@rowcount = 0 UPDATE @t SET column2 = 'Second example' WHERE Column1 = @s

-- new the line exists and column 2 is updated
SELECT * FROM @t

column1     column2
----------- -------------
somevalue   First example

column1     column2
----------- --------------
somevalue   Second example

T-SQL Update else Insert best approach

2 Answers2