Insert/Update

Question

Insert/Update

itlk

Mr or Mrs. 500

Points: 578
More actions
May 28, 2010 at 6:09 pm

#221622

Hi,
In a situation when I want the record to be Inserted if it is new and to be Updated if it is existing is it better to:
1/ try the Update and if it fails than execute Insert
2/ first check if the record Exists and according the result execute Insert or update ?
Thanks,
itlk

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

itlk (5/28/2010)
Hi,
In a situation when I want the record to be Inserted if it is new and to be Updated if it is existing is it better to:
1/ try the Update and if it fails than execute Insert
2/ first check if the record Exists and according the result execute Insert or update ?
Thanks,
itlk

3. update the existing records, then insert the new ones.

-- update existing rows, based on the PK of the table

UPDATE t

SET Column1 = i.Column1 [, Column2 = i.Column2...]

FROM TableToUpdate t

JOIN TableWithNewData i

ON t.PKColumns = i.PKColumns

-- now insert new records by doing a left join, and looking for the null fields (no match)

INSERT INTO TableToUpdate (ColumnList)

SELECT (i.ColumnList)

FROM TableWithNewData i

LEFT JOIN TableToUpdate t

ON t.PKColumns = i.PKColumns

WHERE t.PKColumn1 IS NULL

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 2

Neither - use MERGE instead which will do the insert/update in one command.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Paul White SSC Guru Points: 150467 More actions · Answer 3

You should certainly consider MERGE (if you are using SQL Server 2008) but you need to be careful with either method if there is concurrent activity on the destination table. MERGE has some additional quirks of its own too.

See the following links for important detail:

UPSERT race condition with MERGE

INSERT/UPDATE race condition

Patterns that do not work as expected

Adam Machanic on MERGE

MERGE statement bypasses referential integrity

MERGE and @@ROWCOUNT in triggers

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi