MERGE同步

 
 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