MERGE 解决 当目标表 与原始表 比较异同，判断insert,update,delete 操作

--1Date and Time Types
--following the queryies that it shows different accuracy
select cast('02/07/2007' as datetime)
--The result:2007-02-07 00:00:00.000,Accuracy:millisecond
select cast('02/07/2007' as datetime2)
--The result:2007-02-07 00:00:00.0000000,Accuracy:100 naonesecond
select cast('02/07/2007' as date)
--The result:2007-02-07,Accuracy:1 day
select cast('02/07/2007' as smalldatetime)
--The result:2007-02-07 00:00:00,Accuracy:1 minute

set language british;
select cast('02/07/2007' as datetime)

set language us_english
select cast('02/07/2007' as datetime)


if OBJECT_ID('customer') is not null
drop table Customer
go
create table Customer
(
  cusid int not null,
  CustomeName varchar(20),
  companyname varchar(20),
  constraint PK_cusid primary key (cusid)
)
insert into Customer(cusid,CustomeName,companyname)
select 1,'guo hu','wicresoft'
union all
select 2,'lei hu','Intel'
union all
select 3,'jun wen li','HP'
union all
select 4,'jin hao liu','IBM'
go


if OBJECT_ID('CustomerStage') is not null
drop table CustomerStage
go
create table CustomerStage
(
  cusid int not null,
  CustomeName varchar(20),
  companyname varchar(20),
  constraint PK_cusstageid primary key (cusid)
)
insert into CustomerStage(cusid,CustomeName,companyname)
select 1,'guo hu','Microsoft'
union all
select 3,'jun wen li','alibaba'
union all
select 4,'jin hao liu','siemens'
union all
select 5,'cheng fan','Bank'
go
--Customer为目标表,CustomerStage为原表,当cusid匹配时,更新Customer中的信息,否则插入
MERGE INTO Customer AS TGT
USING CustomerStage AS SCR
ON TGT.cusid=SCR.cusid
WHEN MATCHED THEN
    UPDATE 
    SET  TGT.CustomeName=SCR.CustomeName,
         TGT.companyname=SCR.companyname
WHEN NOT MATCHED THEN
     INSERT (cusid,CustomeName,companyname)
     VALUES(SCR.cusid,SCR.CustomeName,SCR.companyname);
    
--Customer为目标表,CustomerStage为原表,当原表匹配目标表时更新，否则插入, 当目标表不匹配原表时删除
MERGE INTO Customer AS TGT
USING dbo.CustomerStage AS SRC
ON TGT.CUSID=SRC.CUSID
WHEN MATCHED THEN
UPDATE SET
TGT.CustomeName=SRC.CustomeName,
TGT.companyname=SRC.companyname
WHEN NOT MATCHED THEN
INSERT (CUSID,CustomeName,companyname)
VALUES (SRC.CUSID,SRC.CustomeName,SRC.companyname)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;