因为工作中常用到 合并两张表中的数据，主要是写下来给自己备忘，T-SQL 中 MERGE 的用法
WHEN MATCHED THEN UPDATE -- 中加了 后面要更新的列是否都相等，如果相等就没必要进行更新，只会 增加无用功
WHEN NOT MATCHED BY SOURCE THEN DELETE
OUTPUT $ACTION , INSERTED,DELETED, INTO...
总而言之，就是把两张表的 MERGE 写成了一个存储过程，只需要传入相应的表名，列名，以及是否 对目的表进行相应删除的标志位
功能上算是较为灵活，但是肯定有很多没考虑到的地方，咋一看来，代码太不简洁需要重构，等有时间弄吧。本也是为了自己备忘，
WHEN NOT MATCHED BY SOURCE THEN DELETE
OUTPUT $ACTION , INSERTED,DELETED, INTO...
总而言之，就是把两张表的 MERGE 写成了一个存储过程，只需要传入相应的表名，列名，以及是否 对目的表进行相应删除的标志位
功能上算是较为灵活，但是肯定有很多没考虑到的地方，咋一看来，代码太不简洁需要重构，等有时间弄吧。本也是为了自己备忘，
如果对大家有用也很好，欢迎有兴趣的拍砖，共同完善~
下面的的存储过程能够 when matched UPDATE ,
when not matched Insert ,
WHEN NOT MATCHED THEN DELETE
-- 使用 DeleteTargetTabFlag 这个标志位进行选择是否有必要
--从目的表中删除存在于源表而不存在于目的表中的记录，若不传这个值，那么默认是不做删除操作的
USE mydatabase
GO
IF OBJECT_ID('dbo.usp_MergeTabs') IS NOT NULL
DROP PROC dbo.usp_MergeTabs
GO
CREATE PROC dbo.usp_MergeTabs
@TarTabName varchar(128), --- 目的表名
@TarJoinCol1 varchar(20), ---目的表中用来联结的 col
@TarCol2 varchar(20), ---目的表中的要被更新或插入的列 Col2
@TarCol3 varchar(20),---目的表中的要被更新或插入的列 Col3
@SrcTabName varchar(128), --下同
@SrcJoinCol1 varchar(20),
@SrcCol2 varchar(20),
@SrcCol3 varchar(20),
@DeleteTargetFlag bit=0 ---是否从 目的表中删除标志位
AS
IF (NULLIF(@TarTabName,'')+NULLIF(@SrcTabName,'')+NULLIF(@TarJoinCol1,'')+NULLIF(@SrcJoinCol1,'')) IS NULL
BEGIN
RAISERROR('SourceTab,SrcJoinCondition or TargetTab,TarJoinCondition should not be blank',-1,-1)
RETURN;
END
ELSE
Declare @SQLCMD varchar(max) =
'MERGE INTO '+@TarTabName+' AS t
USING '+@SrcTabName+' AS r
ON r.'+@SrcJoinCol1+' = t.'+@TarJoinCol1+'
WHEN MATCHED',
@UpdateSQL varchar(max)='',
@InsertSQL varchar(max)='',
@ValueSQL varchar(max)='',
@UpdateCon varchar(max)='',
@OutputSQL varchar(max)='OUTPUT '+char(10),
@IntoTab varchar(max)='IF OBJECT_ID(''shopping.dbo.OutputTemp'',''U'') IS NOT NULL'+CHAR(13)+'DROP TABLE dbo.OutputTemp'+char(10)+''+char(13)--+' DROP TABLE dbo.OutputTemp'
SET @UpdateSQL=CASE WHEN NULLIF(@TarCol2,'')+NULLIF(@SrcCol2,'') IS NULL
THEN
CASE WHEN NULLIF(@TarCol3,'')+NULLIF(@SrcCol3,'') IS NULL THEN 'Incorrect Parameters'
ELSE 't.'+@TarCol3+' = r.'+@SrcCol3 END
WHEN NULLIF(@TarCol2,'')+NULLIF(@SrcCol2,'') IS NOT NULL THEN
CASE WHEN NULLIF(@TarCol3,'')+NULLIF(@SrcCol3,'') IS NOT NULL THEN
't.'+@TarCol2+' = r.'+@SrcCol2+','+CHAR(13)+'t.'+@TarCol3+' = r.'+@SrcCol3 --char(10)
ELSE 't.'+@TarCol2+' = r.'+@SrcCol2 END
END IF @UpdateSQL!='Incorrect Parameters'
BEGIN
SET @InsertSQL=@TarJoinCol1+','+CASE WHEN NULLIF(@TarCol2,'')+NULLIF(@SrcCol2,'') IS NULL THEN @TarCol3
WHEN NULLIF(@TarCol3,'')+NULLIF(@SrcCol3,'') IS NULL THEN @TarCol2
ELSE @TarCol2+','+@TarCol3
END SET @ValueSQL=@SrcJoinCol1+','+CASE @InsertSQL WHEN @TarJoinCol1+','+@TarCol3 THEN 'r.'+@SrcCol3
WHEN @TarJoinCol1+','+@TarCol2 THEN 'r.'+@SrcCol2
ELSE 'r.'+@SrcCol2+','+'r.'+@SrcCol3
END SET @OutputSQL+=CASE @InsertSQL WHEN @TarJoinCol1+','+@TarCol3 THEN 'inserted.'+@TarJoinCol1+','+'inserted.'+@SrcCol3+' AS New'+@SrcCol3+CHAR(10)+',deleted.'+@SrcCol3++' AS Old'+@SrcCol3
WHEN @TarJoinCol1+','+@TarCol2 THEN 'inserted.'+@TarJoinCol1+','+'inserted.'+@SrcCol2++' AS New'+@SrcCol2+CHAR(13)+',deleted.'+@SrcCol2+' AS Old'+@SrcCol2
ELSE 'inserted.'+@TarJoinCol1+','+'inserted.'+@SrcCol2++' AS New'+@SrcCol2+CHAR(13)+',deleted.'+@SrcCol2+' AS Old'+@SrcCol2+char(10)+',inserted.'+@SrcCol3+' AS New'+@SrcCol3+CHAR(10)+',deleted.'+@SrcCol3++' AS Old'+@SrcCol3
END +',$action AS Action INTO dbo.OutputTemp'
SET @UpdateCon+=' AND '+CASE @InsertSQL WHEN @TarJoinCol1+','+@TarCol3 THEN 'r.'+@SrcCol3+' <> t.'+@TarCol3
WHEN @TarJoinCol1+','+@TarCol2 THEN 'r.'+@SrcCol2+' <> t.'+@TarCol2
ELSE 'r.'+@SrcCol2+' <> t.'+@TarCol2+' OR '+'r.'+@SrcCol3+' <> t.'+@TarCol3
END
SET @IntoTab +='SELECT '+REPLACE(REPLACE(REPLACE(REPLACE(@OutputSQL,'inserted','r'),'deleted','r'),',$action AS Action INTO dbo.OutputTemp',''),'OUTPUT','')+CHAR(10)
+'INTO dbo.OutputTemp FROM '+@TarTabName+' AS r'+CHAR(10)+CHAR(13)
+'ALTER TABLE dbo.OutputTemp ADD [Action] varchar(20)'+CHAR(10)+' '
EXEC (@IntoTab)
print @IntoTab
SET @SQLCMD+=@UpdateCon+' THEN
UPDATE SET
'
IF @DeleteTargetFlag!=0
BEGIN
SET @SQLCMD+=@UpdateSQL+'
WHEN NOT MATCHED THEN
INSERT('+@InsertSQL+')'+'
VALUES('+@ValueSQL+')
WHEN NOT MATCHED BY SOURCE THEN
DELETE;'
END
ELSE
SET @SQLCMD+=@UpdateSQL+'
WHEN NOT MATCHED THEN
INSERT('+@InsertSQL+')'+'
VALUES('+@ValueSQL+')'+CHAR(10)+@OutputSQL+'
;'
print (@SQLCMD)
exec (@SQLCMD)
END
ELSE
RAISERROR(@UpdateSQL,-1,-1);
RETURN; --------------测试数据-------------- IF OBJECT_ID('dbo.TarTab','U') IS NOT NULL
DROP TABLE dbo.TarTab
GO CREATE TABLE dbo.TarTab(StuID int,StuName varchar(15),StuGender char(6))
GO INSERT INTO dbo.TarTab
VALUES(1,'Marry','Female'),
(2,'Tom','Female'),
(3,'Frank','male'),
(4,'Jim','Female'),
(5,'Tom','Female'),
(6,'Sera','Male')
GO IF OBJECT_ID('dbo.SrcTab','U') IS NOT NULL
DROP TABLE dbo.SrcTab
GO CREATE TABLE dbo.SrcTab(StuID int,StuName varchar(15),StuGender char(6))
GO
INSERT INTO dbo.SrcTab
VALUES(1,'Marry','Female'),
(2,'Tom','Female'),
(3,'Frank','male'),
--(4,'Jim','Male'),
--(5,'Tom','Male'),
(6,'Sera','Female'),
(7,'Mongo','FeMale'),
(100,'KangKang','Male')
--SELECT * FROM dbo.TarTab
--select * from dbo.SrcTab
--StuID int,StuName varchar(15),StuGender
exec dbo.usp_MergeTabs
@TarTabName='dbo.TarTab'
,@TarJoinCol1='StuID'
,@TarCol2='StuName'
,@TarCol3='StuGender'
,@SrcTabName='dbo.SrcTab'
,@SrcJoinCol1='StuID'
,@SrcCol2=''
,@SrcCol3='StuGender'
,@DeleteTargetFlag=1 --0 --drop table outputtemp
select * from dbo.OutputTemp select * from dbo.SrcTab