SQL Server如何动态将源表数据全量覆盖同步到目标表

• 变量赋值逻辑错误：原代码从INFORMATION_SCHEMA.COLUMNS查询所有表的字段信息后无过滤直接赋值，变量最终只会取到结果集最后一行的表名和 schema，完全匹配不到传入的源表、目标表参数
• 动态SQL执行语法错误：直接使用EXEC @SQL会将@SQL变量值识别为存储过程名称调用，而非执行SQL语句，正确写法是EXEC (@SQL)或者EXEC sp_executesql @SQL
• 缺少安全转义：未对表名、schema名做转义处理，遇到包含特殊字符的对象名会执行失败
• 可选优化：全量删除目标表数据场景下，TRUNCATE TABLE比DELETE执行效率更高、占用事务日志更少（前提是目标表无外键关联约束）

ALTER PROCEDURE spCustomera
    @Source_table NVARCHAR(100),
    @Dest_table NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Source_Schema NVARCHAR(30), @Target_Schema NVARCHAR(30)
    DECLARE @SQL NVARCHAR(1000)

    -- 单独查询源表所属schema
    SELECT @Source_Schema = TABLE_SCHEMA
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = @Source_table

    IF @Source_Schema IS NULL
    BEGIN
        RAISERROR('源表 %s 不存在', 16, 1, @Source_table)
        RETURN
    END

    -- 单独查询目标表所属schema
    SELECT @Target_Schema = TABLE_SCHEMA
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = @Dest_table

    IF @Target_Schema IS NULL
    BEGIN
        RAISERROR('目标表 %s 不存在', 16, 1, @Dest_table)
        RETURN
    END

    -- 拼接动态SQL，用QUOTENAME转义避免特殊字符问题
    SET @SQL = N'
    TRUNCATE TABLE ' + QUOTENAME(@Target_Schema) + '.' + QUOTENAME(@Dest_table) + N'
    INSERT INTO ' + QUOTENAME(@Target_Schema) + '.' + QUOTENAME(@Dest_table) + N'
    SELECT * FROM ' + QUOTENAME(@Source_Schema) + '.' + QUOTENAME(@Source_table)

    -- 执行动态SQL
    EXEC sp_executesql @SQL
END
GO

-- 调用测试
EXEC spCustomera @Source_table = 'Customer', @Dest_table = 'Customer_new'

执行以下查询即可验证数据是否成功同步：

SELECT * FROM Customer_new

如果目标表存在外键约束无法使用TRUNCATE，将代码中TRUNCATE TABLE替换为DELETE FROM即可正常执行。

内容的提问来源于stack exchange，提问作者Sarthak Gupta