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

巴特西

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

最新文章

热门文章