반응형
/*******************************************************************************************************************
-- Title : [SQL2016] 테이블 단위의 Merge/Upsert 처리
-- Key word : merge upsert
*******************************************************************************************************************/
■ Scripts
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
|
use tempdb;
go
declare @mop_kr table
(skey int, sect varchar(10), val varchar(100))
insert into @mop_kr
select 111,'a1', 'OLD111' union all
select 111,'a2', 'OLD111' union all
select 111,'a3', 'OLD111' union all
select 222,'a1', 'OLD222' union all
select 222,'a3', 'OLD222' union all
select 333,'a2', 'OLD333';
select * from @mop_kr order by skey, sect;
----------------------------------------------
declare @py_dataframe table
(skey int, sect varchar(10), val varchar(100))
insert into @py_dataframe
select 222,'a1', 'UPD222' union all
select 222,'a2', 'INS222' union all
select 222,'a3', 'UPD222' union all
select 333,'a1', 'INS333' union all
select 333,'a2', 'UPD333' union all
select 333,'a3', 'INS333';
select * from @py_dataframe order by skey, sect;
----------------------------------------------
MERGE INTO @mop_kr mop
USING
(select * from @py_dataframe) AS py
ON mop.skey = py.skey
AND mop.sect = py.sect
WHEN MATCHED THEN
UPDATE SET
val = py.val
WHEN NOT MATCHED THEN
INSERT (skey, sect, val)
VALUES (py.skey, py.sect, py.val);
select * from @mop_kr order by skey, sect;
|
cs |
반응형