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]


USE [master]
GO
/****** Object: Database [MyDatabase] Script Date: 03/16/2008 10:55:35 ******/
IF EXISTS (SELECT name FROM sys.databases WHERE name = N’MyDatabase’)
DROP DATABASE [MyDatabase]
GO
create database [MyDatabase]
go



Fig 1.0

Step 2

Let’s assume that we have the following three tables, as shown below. [Refer
Fig 1.1]


USE [MyDatabase]
GO

/****** Object: Table [dbo].[MyTable] Script Date: 03/16/2008 11:07:24 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[MyTable]’) AND type in (N’U’))
DROP TABLE [dbo].[MyTable]
GO

create table MyTable (id int primary key, name varchar(100),salary money)
go
insert into MyTable select 1,’Catherine Donnel’,200000
insert into MyTable select 2,’Stacey Kost’,150000
insert into MyTable select 3,’Jason Leanos’,36000
insert into MyTable select 4,’Catherine O”Donnel’,20000
insert into MyTable select 5,’Rainbow Dance’,20000

go
USE [MyDatabase]
GO

/****** Object: Table [dbo].[MyTable2] Script Date: 03/16/2008 11:07:36 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[MyTable2]’) AND type in (N’U’))
DROP TABLE [dbo].[MyTable2]
GO

create table MyTable2 (id int primary key, name varchar(100),salary money)
go
insert into MyTable2 select 1,’Catherine O”Donnel’,220000
insert into MyTable2 select 2,’Stacey Kostue’,230000
insert into MyTable2 select 4,’Catherine Bonaparte’,20000
insert into MyTable2 select 9,’Irina Zolotrova’,40000
insert into MyTable2 select 5,’Eva Jane’,40034
go



Fig 1.1

Step 3

Now let’s query both of the tables to see the difference between them.


USE [MyDatabase]
GO
Select * from MyTable
Go

Results:

id, name, salary
1, Catherine Donnel, 200000.00
2, Stacey Kost, 150000.00
3, Jason Leanos, 36000.00
4, Catherine O'Donnel, 20000.00
5, Rainbow Dance, 20000.00
 
(5 row(s) affected)


USE [MyDatabase]
GO
Select * from MyTable2
GO

Results:

id, name, salary
1, Catherine O'Donnel, 220000.00
2, Stacey Kostue, 230000.00
4, Catherine Bonaparte, 20000.00
5, Eva Jane, 40034.00
9, Irina Zolotrova, 40000.00
 
(5 row(s) affected)

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);



Fig 1.3

Step 5

Now let’s query the target table, MyTable, as shown below. [Refer Fig
1.4]


USE [MyDatabase]
GO
Select * from MyTable
GO

Results:

id, name, salary
1, Catherine O'Donnel, 220000.00
2, Stacey Kostue, 230000.00
3, Jason Leanos, 36000.00
4, Catherine Bonaparte, 20000.00
5, Eva Jane, 40034.00
9, Irina Zolotrova, 40000.00
 
(6 row(s) affected)



Fig 1.4

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.

»


See All Articles by Columnist
MAK

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles