Featured Database Articles

UPSERT Functionality in SQL Server 2008

The UPSERT command inserts rows that dont
exist and updates the rows that do exist. The Word UPSERT is a fusion of the
words UPDATE and INSERT. UPSERT was officially introduced in the SQL:2003
standard.

IF FOUND
THEN UPDATE
ELSE
INSERT;

In SQL Server 2008, Microsoft introduces
the UPSERT functionality through the MERGE command.

Until now, this UPSERT functionality could
only be made available in SQL Server through stored procedures and cursors,
etc.

This article illustrates how to take
advantage of the UPSERT functionality via the MERGE command.

Note: This article is written based on
SQL Server 2008 CTP6.

Step 1

Lets assume that we have a database [MyDatabase] as shown below. [Refer
Fig 1.0]

From the results, we can easily
understand that if we want to merge all of the rows from Mytable2 to Mytable,
then the following updates and following inserts are supposed to be executed. [Refer
Fig 1.2]

Fig 1.2

Step 4

Now lets UPSERT the table by using the following
merge command to merge the content of the table Mytable2 to the table Mytable. [Refer
Fig 1.3]

USE [MyDatabase]
GO
merge into mytable as Target
using mytable2 as Source
on Target.id=Source.id
when matched then
update set Target.name=Source.name,
Target.Salary = Source.Salary
when not matched then
insert (id,name,salary) values (Source.id,Source.name,Source.Salary);

From the results, we see that all of the
existing rows in the table, Mytable, have been updated with the data from the
table, Mytable2. In addition, we see that any new rows that were present in the
Mytable2 table have been inserted to the table, MyTable.

Basically, the merge command executed the following algorithm.

Note: The below syntax is not a transact SQL command. It is just an
algorithm.

If target.ID = 1 is found in Source.id = 1 {found}
then
update target
set target.name {Catherine Donnel} = source.name {Catherine O''Donnel}
Target.salary {200000}=Source.salary {220000}
end if
If target.ID = 2 is found in Source.id = 2 {found}
then
update target
set target.name {Stacey Kost} = source.name {Stacey Kostue}
Target.salary {150000.00} = Source.salary {230000.00}
end if
If target.ID=3 is found in Source.Id = NULL {not found}
then
end if
If target.ID=4 is found in Source.id = 4 {found}
then
update target
set target.name {Catherine O''Donnel} = source.name {Catherine Bonaparte}
Target.salary {20000.00} = Source.salary {20000.00}
end if
If target.ID=5 is found in Source.id = 5 {found}
then
update target
set target.name {Rainbow Dance} = source.name {Eva Jane}
Target.salary {20000.00} = Source.salary {40034.00}
end if
If target.ID not found in Source.id = 9 {found}
then
insert into target (id,name,salary) select source (9, Irina Zolotrova, 40000.00)
end if

Conclusion

This article has illustrated the functionality of UPSERT via the MERGE
command in SQL Server 2008.