sql server实现copy data功能的存储过程（公共代码）

目标：
源表到目标表数据的复制（copy data）

在用adf的时候中间有个pipeline是做upsert工作。

这里需要调用一个存储过程，因为有很多表，所以需要写一个公用的存储过程。

参考：https://www.taygan.co/blog/2018/04/20/upsert-to-azure-sql-db-with-azure-data-factory

CREATE PROCEDURE proc_update          //需要传入目标表的名字
    @target_table_name varchar(200)
AS
BEGIN

    DECLARE @primary_key_name varchar(50) //表主键的名字
    DECLARE @source_table_name varchar(220) //源表的名字
    DECLARE @upsert_sql nvarchar(max)    //sql字符串

    SET @source_table_name = 'shadow_' + @target_table_name //源表的名字由目标表名拼写出来
    SET @primary_key_name = (
        SELECT COLUMN_NAME
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
        WHERE TABLE_NAME = @target_table_name
    )                                                 //获取主键名字

    DECLARE @column_name_str nvarchar(max)
    DECLARE @column_name_str_values nvarchar(max)
    DECLARE @column_name_without_pri_key_str nvarchar(max)
　　//获取目标表列名集合并用逗号分隔。比如(id,name,age)
    SELECT @column_name_str = name FROM (
        SELECT stuff(
            (
                SELECT ',' + name
                FROM sys.columns
                WHERE object_id = object_id(@target_table_name)
                for xml path('')
            ), 1, 1, '') as name
    ) as n
　　//获取源表的列集合并用逗号（，）分隔。比如（S.id,S.name,S.age）
    SELECT @column_name_str_values = name FROM (
        SELECT stuff(
            (
                SELECT ',S.' + name
                FROM sys.columns
                WHERE object_id = object_id(@target_table_name)
                for xml path('')
            ), 1, 1, '') as name
    ) as n
　　//更新的时候不需要更新主键，获取update set 语句的字符串拼接（比如 T.name = S.name, T.age = S.age）
    SELECT @column_name_without_pri_key_str = name FROM (
        SELECT stuff(
            (
                SELECT ',T.' + name + '=' + 'S.' + name
                FROM sys.columns
                WHERE object_id = object_id(@target_table_name) and columns.name <> @primary_key_name
                for xml path('')
            ), 1, 1, '') as name
        ) as n
　　 // 这里是主要逻辑 用到了merge方法（自行查阅用法）
    SET @upsert_sql = ' MERGE INTO ' + @target_table_name + ' AS T' +
                      ' USING (SELECT * FROM ' + @source_table_name + ' ) AS S' +
                      ' ON T.'+ @primary_key_name + ' = S.' + @primary_key_name +
                      ' WHEN MATCHED THEN' +
                      ' UPDATE SET ' + @column_name_without_pri_key_str +
                      ' WHEN NOT MATCHED THEN' +
                      ' INSERT (' + @column_name_str + ')' +
                      ' VALUES (' + @column_name_str_values + ');' +
                      ' TRUNCATE TABLE ' + @source_table_name + ';'
    exec(@upsert_sql)

END
go