SQL合并数据 Meger

--表结构
SELECT FOrderNumber,FGameID,FStatus,FUserID,FCompanyID
INTO #target
FROM dbo.TOrders
WHERE 1=2

SELECT FOrderNumber,FGameID,FStatus,FUserID,FCompanyID
INTO #source
FROM dbo.TOrders
WHERE 1=2


--插入测试数据
INSERT #target(FOrderNumber,FGameID,FUserID,FCompanyID,FStatus)
VALUES
('20160121150',1,43610,36052,1),
('20151105060',2,43620,36052,3),
('20160330070',3,43630,36052,5),
('20160331180',4,43640,36052,7),
('20151205390',5,43650,36052,9)

INSERT #source(FOrderNumber,FGameID,FUserID,FCompanyID,FStatus)
VALUES
('20160121150',6,43660,36052,2),
('20151105060',7,43670,36052,4),
('20160121093',8,43680,36052,6),
('20160219102',9,43690,36052,8),
('20160621132',10,43700,36052,10)

 

 

 

 

 

--1
MERGE INTO #target AS t
USING #source AS s
ON t.FOrderNumber=s.FOrderNumber --匹配条件

--匹配的时候,更新
WHEN MATCHED THEN
UPDATE SET t.FGameID=s.FGameID,t.FStatus=s.FStatus

--插入(源表有,目标表没有)
WHEN NOT MATCHED THEN
INSERT(FOrderNumber,FGameID,FUserID,FCompanyID,FStatus)
VALUES(s.FOrderNumber,s.FGameID,s.FUserID,s.FCompanyID,s.FStatus);

 

 

--2
MERGE INTO #target AS t
USING #source AS s
ON t.FOrderNumber=s.FOrderNumber --匹配条件

--匹配的时候,更新
WHEN MATCHED THEN
UPDATE SET t.FGameID=s.FGameID,t.FStatus=s.FStatus

--删除
WHEN NOT MATCHED BY SOURCE THEN DELETE;

 

 

--3
MERGE INTO #target AS t
USING #source AS s
ON t.FOrderNumber=s.FOrderNumber --匹配条件

--匹配的时候,更新
WHEN MATCHED THEN
UPDATE SET t.FGameID=s.FGameID,t.FStatus=s.FStatus

--插入(源表有,目标表没有)
WHEN NOT MATCHED THEN
INSERT(FOrderNumber,FGameID,FUserID,FCompanyID,FStatus)
VALUES(s.FOrderNumber,s.FGameID,s.FUserID,s.FCompanyID,s.FStatus)

--删除
WHEN NOT MATCHED BY SOURCE THEN DELETE;

 

扩展阅读