#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 {{{ }}} ==== 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]