UPSERT Functionality in SQL Server 2008
By Muthusamy Anantha Kumar aka The MAK
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.
<PRE style="BACKGROUND: none transparent scroll repeat 0% 0%; COLOR: green">IF FOUND THEN UPDATEELSE INSERT;</PRE>
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]
<PRE style="BACKGROUND: none transparent scroll repeat 0% 0%; COLOR: green" hasbox="2">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]GOcreate database [MyDatabase]go</PRE>
Fig 1.0
Step 2
Let's assume that we have the following three tables, as shown below. [Refer Fig 1.1]
<PRE style="BACKGROUND: none transparent scroll repeat 0% 0%; COLOR: green" hasbox="2">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)goinsert into MyTable select 1,'Catherine Donnel',200000insert into MyTable select 2,'Stacey Kost',150000insert into MyTable select 3,'Jason Leanos',36000insert into MyTable select 4,'Catherine O''Donnel',20000insert into MyTable select 5,'Rainbow Dance',20000 goUSE [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)goinsert into MyTable2 select 1,'Catherine O''Donnel',220000insert into MyTable2 select 2,'Stacey Kostue',230000insert into MyTable2 select 4,'Catherine Bonaparte',20000insert into MyTable2 select 9,'Irina Zolotrova',40000insert into MyTable2 select 5,'Eva Jane',40034go</PRE>
Fig 1.1
Step 3
Now let's query both of the tables to see the difference between them.
<PRE style="BACKGROUND: none transparent scroll repeat 0% 0%; COLOR: green" hasbox="2">USE [MyDatabase]GOSelect * from MyTableGo</PRE><PRE>Results:</PRE><PRE style="COLOR: red; BACKGROUND-COLOR: transparent">id, name, salary1, Catherine Donnel, 200000.002, Stacey Kost, 150000.003, Jason Leanos, 36000.004, Catherine O'Donnel, 20000.005, Rainbow Dance, 20000.00 (5 row(s) affected)</PRE><PRE style="BACKGROUND: none transparent scroll repeat 0% 0%; COLOR: green">USE [MyDatabase]GOSelect * from MyTable2GO</PRE>
Results:
<PRE style="COLOR: red; BACKGROUND-COLOR: transparent">id, name, salary1, Catherine O'Donnel, 220000.002, Stacey Kostue, 230000.004, Catherine Bonaparte, 20000.005, Eva Jane, 40034.009, Irina Zolotrova, 40000.00 (5 row(s) affected)</PRE>
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]
<PRE style="BACKGROUND: none transparent scroll repeat 0% 0%; COLOR: green" hasbox="2">USE [MyDatabase]GOmerge into mytable as Targetusing mytable2 as Sourceon Target.id=Source.idwhen matched then update set Target.name=Source.name,Target.Salary = Source.Salarywhen not matched theninsert (id,name,salary) values (Source.id,Source.name,Source.Salary);</PRE>
Fig 1.3
Step 5
Now let's query the target table, MyTable, as shown below. [Refer Fig 1.4]
<PRE style="BACKGROUND: none transparent scroll repeat 0% 0%; COLOR: green">USE [MyDatabase]GOSelect * from MyTableGO</PRE>
Results:
<PRE style="COLOR: red; BACKGROUND-COLOR: transparent">id, name, salary1, Catherine O'Donnel, 220000.002, Stacey Kostue, 230000.003, Jason Leanos, 36000.004, Catherine Bonaparte, 20000.005, Eva Jane, 40034.009, Irina Zolotrova, 40000.00 (6 row(s) affected)</PRE>
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.
<PRE style="BACKGROUND: none transparent scroll repeat 0% 0%; COLOR: blue" hasbox="2">If target.ID = 1 is found in Source.id = 1 {found}thenupdate 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}thenupdate 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}thenupdate 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}thenupdate 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}theninsert into target (id,name,salary) select source (9, Irina Zolotrova, 40000.00) end if</PRE>
Conclusion
This article has illustrated the functionality of UPSERT via the MERGE command in SQL Server 2008.
댓글 없음:
댓글 쓰기