How do I write a simple insert or update in T-SQL?

Question

I have a process that I do not want to track if something is a create or an update. Tracking would be complex. I would like to perform a create OR update. The schema is like...

col1 varchar() (PK)
col2 varchar() (PK)
col3 varchar() (PK)
col4 varchar()

I am thinking about doing

TRY
{
    INSERT ...
}
CATCH(DuplicateKeyException)
{
    UPDATE ...
}

What do you suggest?

I want to ensure that I understand the other top voted answer. Given my schema, the UPDATE always occurs (even with an INSERT), but the insert only occurs where it does not exist?

 //UPSERT
INSERT INTO [table]
SELECT [col1] = @col1, [col2] = @col2, [col3] = @col3, [col4] = @col4
FROM [table]
WHERE NOT EXISTS (
    -- race condition risk here?
    SELECT  1 
    FROM [table] 
    WHERE [col1] = @col1 
        AND [col2] = @col2
        AND [col3] = @col3
)

UPDATE [table]
SET [col4] = @col4
WHERE [col1] = @col1 
    AND [col2] = @col2
    AND [col3] = @col3

`Merge` not available then. See http://stackoverflow.com/questions/2522379/atomic-upsert-in-sql-server-2005/2525343#2525343 — Martin Smith, Aug 31 '11 at 19:26
@JNK - Maybe. That question specifically addresses SQL 2005 though. — P.Brian.Mackey, Aug 31 '11 at 19:33
@P.Brian - look at it and see if it takes care of your issue :) — JNK, Aug 31 '11 at 19:34

Christopher Klein · Accepted Answer · 2011-09-01T13:42:08.770

6

update table1 set col1=1,col2=2,col3=3 where a=1
if @@ROWCOUNT=0
   insert into table1 (col1,col2,col3) values (1,2,3)

I think its quicker than checking existance first.

edited Sep 01 '11 at 13:42

answered Aug 31 '11 at 19:50

Christopher Klein

2,773
4
39
61

+1 - Dont use the link from @Martin saying this is a DUP. The UPSERT code there causes intermittent errors that are very hard to pinpoint. If you want to use that method, then see my answer here http://stackoverflow.com/questions/7263445/sql-c-primary-key-error-on-upsert – P.Brian.Mackey Sep 01 '11 at 15:20
@P.Brian.Mackey I get the distinct impression you didn't read that answer properly but just blindly copied the code. The text explains why it doesn't work and gives an alternative. – Martin Smith Sep 02 '11 at 12:25
@Martin - I'm no SQL expert, but I don't agree with his logic. I don't see why it's necessary to go through a page worth of "atomic operation" detail when all you need to do is take off the FROM. To me, that answer is the definition of over-engineering. – P.Brian.Mackey Sep 02 '11 at 19:09

Justin Helgerson · Answer 2 · 2011-08-31T19:51:43.283

0

You can use the MERGE statement (if you're using T-SQL) or build a query that does an INSERT or UPDATE based upon the existence of your key.

Edit: OP has edited his question. Yes, you'll technically be running the UPDATE and INSERT every time this query is executed, but you'll only touch the table with one of those statements depending on which criteria is met.

edited Aug 31 '11 at 19:51

answered Aug 31 '11 at 19:23

Justin Helgerson

24,900
17
97
124

How do I write a simple insert or update in T-SQL?

2 Answers2