Saturday 10 February 2007

the UPSERT

The concept of an UPSERT is to UPdate an existing data row (if present) or inSERT it (if not). These are examples only, assume variable declaration and parameters have already been sorted. Upsert example #1
1) check for row existance,
2) if exists - update row,
3) if doesnt - insert row.

if EXISTS (SELECT * FROM dbo.tblContact WITH (READUNCOMMITTED) Where ContactID = @contactID)
BEGIN
UPDATE dbo.tblContact
SET Surname = @Surname
 , Forename = @Forename
 , EmailAdd = @EmailAdd
WHERE ContactID = @contactID
END
ELSE
BEGIN
INSERT dbo.tblContact
 (ContactID
 ,Surname
 ,Forename
 ,EmailAdd)
 VALUES
 (@ContactID
 ,@Surname
 ,@Forename
 ,@EmailAdd)
END


Upsert example #2
1) attempt to update row
2) if no rows updated, insert row.
UPDATE dbo.tblContact
SET Surname = @Surname
, Forename = @Forename
, EmailAdd = @EmailAdd
WHERE ContactID = @contactID
IF @@rowcount = 0
BEGIN
INSERT dbo.tblContact
 (ContactID
 ,Surname
 ,Forename
 ,EmailAdd)
 VALUES
 (@ContactID
 ,@Surname
 ,@Forename
 ,@EmailAdd)
END


Example 1 always hits the database twice, with 2 I/O operations. The first for checking, the second for the correct DML command.

Example 2 gets away with 1 I/O operation for rows that are updates, and 2 for rows that are inserts. This is an improvement over example 1 therefore.

No comments: