Contents

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 企企.

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>

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

4 谿瑚襭 #