#title MERGE
[[TableOfContents]]
merge가 대체적으로 insert/update 보다 성능이 꾸졌다고한다. 편리함이 성능을 커버한다고 생각하면 써라! 성능이 SW의 모든 것은 아니다.
==== 예제 ====
'있으면 갱신하고, 없으면 삽입하라'라는 로직에 적합한 구문이다. 즉, 'IF Found THEN UPDATE ELSE INSERT' 이다. 예제는 Row Constructors에서 만든 테이블로 하겠다.
{{{
select * from target_table;
/*
id pw
---------- ----------
dwa 1234
yasi 4567
lk lk123
*/
--현재 source에는 데이터가 있고, target에는 데이터가 없다.
insert audit(action, insert_id, insert_pw, delete_id, delete_pw)
select action, insert_id, insert_pw, delete_id, delete_pw
from (
merge --top (100) percent
target_table a using source_table b
on a.id = b.id
when matched then
update set
a.id = b.id
, a.pw = b.pw
when not matched by target then
insert values (id, pw)
when not matched by source then
delete
output $action, inserted.*, deleted.*
) t (action, insert_id, insert_pw, delete_id, delete_pw);
select * from audit;
/*
action insert_id insert_pw delete_id delete_pw
-------------------------------------------------- ---------- ---------- ---------- ----------
INSERT dwa 1234 NULL NULL
INSERT yasi 4567 NULL NULL
INSERT lk lk123 NULL NULL
*/
}}}
매치되지 않는 row를 삽입해 보자.
{{{
insert source_table values ('dwa2', '1234');
insert target_table values ('dwa3', '1234');
/*
select * from source_table;
select * from target_table;
id pw
---------- ----------
dwa 1234
yasi 4567
lk lk123
dwa2 1234 <--- not matched
id pw
---------- ----------
dwa 1234
yasi 4567
lk lk123
dwa3 1234 <--- not matched
*/
}}}
not matched 로우들에 대한 처리를 해보자.
{{{
insert audit(action, insert_id, insert_pw, delete_id, delete_pw)
select action, insert_id, insert_pw, delete_id, delete_pw
from (
merge --top (100) percent
target_table a using source_table b
on a.id = b.id
when matched then
update set
a.id = b.id
, a.pw = b.pw
when not matched by target then
insert values (id, pw)
when not matched by source then
delete
output $action, inserted.*, deleted.*
) t (action, insert_id, insert_pw, delete_id, delete_pw);
select * from audit;
/*
action insert_id insert_pw delete_id delete_pw
-------------------------------------------------- ---------- ---------- ---------- ----------
INSERT dwa 1234 NULL NULL
INSERT yasi 4567 NULL NULL
INSERT lk lk123 NULL NULL
INSERT dwa2 1234 NULL NULL
UPDATE dwa 1234 dwa 1234
UPDATE yasi 4567 yasi 4567
UPDATE lk lk123 lk lk123
DELETE NULL NULL dwa3 1234
*/
select * from source_table;
select * from target_table;
/*
id pw
---------- ----------
dwa 1234
yasi 4567
lk lk123
dwa2 1234
id pw
---------- ----------
dwa 1234
yasi 4567
lk lk123
dwa2 1234
dwa2는 traget_table에 없으므로 insert, dwa3는 source_table not matched이므로 target_table에서 delete
*/
}}}
다음과 같이 'AND'로 조건을 추가 할 수도 있다.
{{{
insert audit(action, insert_id, insert_pw, delete_id, delete_pw)
select action, insert_id, insert_pw, delete_id, delete_pw
from (
merge --top (100) percent
target_table a using source_table b
on a.id = b.id
when matched and a.id like 'dw%' then --조건추가 한 것
update set
a.id = b.id
, a.pw = b.pw
when not matched by target then
insert values (id, pw)
when not matched by source then
delete
output $action, inserted.*, deleted.*
) t (action, insert_id, insert_pw, delete_id, delete_pw);
/* 참고..
insert a(id, pw)
select id, pw
from (
delete from target_table
output deleted.*
) t
go
--삭제된 것을 조회
create proc usp_teset
as
delete from a
output deleted.*
go
*/
}}}
{{{
set nocount on;
create table #t
(
id int
, name varchar(max)
);
go
insert #t values (1,'Jim'),(2,'Sarah'),(3,'Hels');
go
DECLARE @rowcounts TABLE
(
mergeAction nvarchar(10)
);
declare @insertCount int, @updateCount int, @deleteCount int;
merge into #t as tgt
using ( select 1 as id, 'James' as name
union
select 2, 'Sarah'
union
select 3, 'Helen'
union
select 4, 'Jack'
union
select 5, 'Annie') as src
on tgt.id = src.id
when matched and tgt.name = src.name
THEN DELETE
when matched and tgt.name <> src.name
THEN UPDATE SET tgt.name = src.name
when not matched
THEN insert values (src.id, src.name)
OUTPUT $action into @rowcounts;
select @insertcount=[INSERT]
, @updatecount=[UPDATE]
, @deletecount=[DELETE]
from (
select mergeAction,1 rows
from @rowcounts
)p
pivot
(
count(rows)
FOR mergeAction IN
( [INSERT], [UPDATE], [DELETE])
) as pvt
;
drop table #t;
print '@insertcount = ' + cast(@insertcount as varchar);
print '@updatecount = ' + cast(@updatecount as varchar);
print '@deletecount = ' + cast(@deletecount as varchar);
}}}
{{{
/*
drop table #target
drop table #source
*/
create table #target(seq int)
create table #source(seq int)
insert #target values(1)
insert #target values(2)
insert #target values(3)
insert #source values(1)
insert #source values(4)
merge #target a
using #source b
on a.seq = b.seq
when matched then
update set
a.seq = b.seq
when not matched by source and a.seq > 2 then
delete
when not matched by target then
insert (seq)
values (b.seq)
;
select * from #target
select * from #source
}}}
update, delete, insert되는 대상은 모두 target 테이블이다.
==== OpenRowSet의 이용 ====
{{{
USE AdventureWorks2008R2;
GO
CREATE TABLE dbo.Stock (StockName nvarchar(50) PRIMARY KEY, Qty int CHECK (Qty > 0));
GO
MERGE dbo.Stock AS s
USING OPENROWSET (
BULK 'C:\SQLFiles\StockData.txt',
FORMATFILE = 'C:\SQLFiles\BulkloadFormatFile.xml',
ROWS_PER_BATCH = 15000,
ORDER (Stock) UNIQUE) AS b
ON s.StockName = b.Stock
WHEN MATCHED AND (Qty + Delta = 0) THEN DELETE
WHEN MATCHED THEN UPDATE SET Qty += Delta
WHEN NOT MATCHED THEN INSERT VALUES (Stock, Delta);
GO
}}}
BulkloadFormatFile.xml
{{{
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="25"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="5"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="Stock" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="2" NAME="Delta" xsi:type="SQLSMALLINT"/>
</ROW>
</BCPFORMAT>
}}}
==== SCD2 ====
{{{
drop table #source
drop table #target
create table #source (id int, gb int)
insert #source values(1, 1)
insert #source values(2, 2)
create table #target(seq int identity(1,1), id int, gb int, bdt date, edt date)
--초기적재
declare
@begin_dt date
if not exists (select * from #target)
set @begin_dt = '20000101'
else
set @begin_dt = getdate()
merge #target a
using #source b
on a.id = b.id
and a.gb = b.gb
when matched and a.edt = '99991231' and a.id = b.id then
update set
a.edt = @begin_dt
when not matched by target then
insert(id, gb, bdt, edt)
values(b.id, b.gb, @begin_dt, '99991231')
output $action, inserted.*, deleted.*;
--id=1의 gb가 4로 변경되었다면?
update #source
set gb = 4
where id = 1
/*
select * from #source
id gb
1 4
2 2
*/
declare
@begin_dt2 date
if not exists (select * from #target)
set @begin_dt2 = '20000101'
else
set @begin_dt2 = getdate()
merge #target a
using #source b
on a.id = b.id
and a.gb = b.gb
and a.edt = '99991231'
when not matched by target then
insert(id, gb, bdt, edt)
values(b.id, b.gb, @begin_dt2, '99991231')
when not matched by source and a.edt = '99991231' then
update set
a.edt = @begin_dt2
output $action, inserted.*, deleted.*;
/*
select * from #target
seq id gb bdt edt
1 1 1 2000-01-01 2010-11-05
2 2 2 2000-01-01 9999-12-31
3 1 4 2010-11-05 9999-12-31
*/
--id=1의 gb가 5로 변경되었다면?
update #source
set gb = 5
where id = 1
declare
@begin_dt3 date
if not exists (select * from #target)
set @begin_dt3 = '20000101'
else
set @begin_dt3 = getdate()
merge #target a --만약 minimal logging 하고 싶으면.. with (TABLOCK) a 와 같이 테이블 락을 잡아준다.
using #source b
on a.id = b.id
and a.gb = b.gb
and a.edt = '99991231'
when not matched by target then
insert(id, gb, bdt, edt)
values(b.id, b.gb, @begin_dt3, '99991231')
when not matched by source and a.edt = '99991231' then
update set
a.edt = @begin_dt3
output $action, inserted.*, deleted.*;
/*
select * from #target
seq id gb bdt edt
1 1 1 2000-01-01 2010-11-05
2 2 2 2000-01-01 9999-12-31
3 1 4 2010-11-05 2010-11-05
4 1 5 2010-11-05 9999-12-31
*/
}}}
==== 참고자료 ====
* [http://www.sqlservercentral.com/articles/MERGE/103127/ Performance of the SQL MERGE vs. INSERT/UPDATE]
* [attachment:MERGE/Customer_SCD_Example_Daily_Process_MERGE_script.zip Using the SQL MERGE Statement for Slowly Changing Dimension Processing]
* [http://www.simple-talk.com/sql/learn-sql-server/the-merge-statement-in-sql-server-2008/ The MERGE Statement in SQL Server 2008]
* [http://www.sqlservercentral.com/articles/T-SQL/66066/ Dynamic SQL Merge]
* [http://sqlblog.com/blogs/jamie_thomson/archive/2009/08/30/extracting-insert-update-delete-rowcounts-from-t-sql-merge.aspx Extracting insert, update, delete rowcounts from T-SQL MERGE]
* [http://blogs.msdn.com/b/sqlserverstorageengine/archive/2010/06/03/minimal-logging-and-merge-statement.aspx Minimal logging and MERGE statement]