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 企企.
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>
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]](/moniwiki/imgs/http.png)
Performance of the SQL MERGE vs. INSERT/UPDATE(http://www.sqlservercentral.com/articles/MERGE/103127/)
Using the SQL MERGE Statement for Slowly Changing Dimension Processing