DataBaser.Net: MERGE

1 예제
2 OpenRowSet의 이용
3 SCD2
4 참고자료

merge가 대체적으로 insert/update 보다 성능이 꾸졌다고한다. 편리함이 성능을 커버한다고 생각하면 써라! 성능이 SW의 모든 것은 아니다.

1 예제 #

'있으면 갱신하고, 없으면 삽입하라'라는 로직에 적합한 구문이다. 즉, '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 테이블이다.

[edit]

2 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>

[edit]

3 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
*/

[edit]

Contents

1 예제 #

2 OpenRowSet의 이용 #

3 SCD2 #

4 참고자료 #