Merge OUTPUT 高级用法综合写的一个MergeTab的存储过程

因为工作中常用到 合并两张表中的数据， 主要是写下来给自己备忘，T-SQL 中 MERGE 的用法

WHEN MATCHED THEN UPDATE -- 中加了 后面要更新的列是否都相等，如果相等就没必要进行更新，只会 增加无用功
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

　　

 

转载于:https://www.cnblogs.com/Frank99/p/5400002.html