SQL合并更新与插入-优快云博客

本文介绍了一种使用T-SQL实现的数据同步方法，通过MERGE语句结合动态SQL来完成源表与目标表之间的数据同步操作，包括更新现有记录及插入新记录。此方法适用于需要在两个表间进行高效数据同步的场景。

alter proc test @sourcetablename varchar(30),
@targettablename varchar(200),
@id varchar(8)
as
declare @cols varchar(max);
declare @cols1 varchar(max);
declare @cols2 varchar(max);

with cols
        as (
            select columns.name,'s.'+columns.name name1,
                'd.'+columns.name+'='+'s.'+columns.name name2
              from sys.columns
                join sys.objects
                on sys.columns.object_id=sys.objects.object_id
                   and objects.name=@sourcetablename
           )
select @cols=(stuff((
                       select ',' + name from cols t
                      for
                       xml path('')
                      ),1,1,'')),@cols1=(stuff((
                                                select ',' + name1 from cols t
                                               for
                                                xml path('')
                                               ),1,1,'')),
      @cols2=(stuff((
                     select ',' + name2 from cols t
                    for
                     xml path('')
                    ),1,1,''))


select @cols2=replace(@cols2,('d.'+@id+'=s.'+@id+','),'')

declare @sql varchar(max)

set @sql='set identity_insert '+@targettablename+' ON;'+'

MERGE '+@targettablename+' with (TABLOCKX)   AS d
USING '+@sourcetablename+' AS s
ON s.'+@id+'=d.'+@id+'
WHEN NOT MATCHED BY TARGET
    THEN
    INSERT ('+@cols+')
      VALUES ('+@cols1+')
WHEN NOT MATCHED BY SOURCE
    THEN
    DELETE
WHEN MATCHED
    THEN
    UPDATE
      SET '+@cols2+'

      ;'+'set identity_insert '+@targettablename+' off'

exec    (@sql)


go
exec test 'Student','[Student2]','StuId'

--select obj.name,*
-- from sys.columns col
--   join sys.objects obj
--   on col.object_id=obj.object_id
-- -- where obj.name='Student'
--   and is_identity=1