CODEWITHSUNDEEP
sqlsql-server-2000upsert
P.Brian.Mackey
P.Brian.Mackey

Reputation: 44295

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

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

Upvotes: 2

Views: 3048

Answers (2)

Christopher Klein
Christopher Klein

Reputation: 2793

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.

Upvotes: 6

Justin Helgerson
Justin Helgerson

Reputation: 25551

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.

Upvotes: 0

Related Questions