Snowflake中模拟INSERT BY NAME行为的SQL等价实现方案

DuckDB的INSERT BY NAME语法允许将SELECT结果按列名匹配目标表插入，无需严格对应列顺序，但Snowflake原生不支持该语法。使用MERGE INTO ... INSERT ALL BY NAME的方案虽能实现功能，但会因MERGE语句持有锁，阻碍同类型语句的并行执行。以下是两种无锁替代方案：

问题场景回顾 #

原场景示例：

CREATE TABLE tab(col1 INT, col2 STRING, col3 DATE);

-- 直接INSERT因列顺序不匹配报错，COL1会被传入DATE类型值
INSERT INTO tab
SELECT CURRENT_DATE() AS col3, 1 AS col1, 'a' AS col2;
-- SQL编译错误：表达式类型与列数据类型不匹配，COL1列期望NUMBER(38,0)但得到DATE

-- Snowflake不支持BY NAME修饰符
INSERT INTO tab BY NAME
SELECT CURRENT_DATE() AS col3, 1 AS col1, 'a' AS col2;
-- SQL编译错误：意外的'BY'。

期望实现按列名匹配插入，最终表数据为：

+------+------+------------+
| col1 | col2 |    col3    |
+------+------+------------+
|    1 | a    | 2025-10-19 |
+------+------+------------+

方案一：动态生成INSERT语句（自动化方案） #

利用Snowflake的INFORMATION_SCHEMA.COLUMNS元数据视图，动态获取目标表的列顺序，生成匹配列名的INSERT语句，全程使用普通INSERT避免锁问题。

步骤1：创建自动化存储过程 #

CREATE OR REPLACE PROCEDURE INSERT_BY_NAME(target_table VARCHAR, src_query VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
  // 获取目标表的列列表（按表定义顺序）
  const getColumnsSql = `
    SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = CURRENT_SCHEMA()
      AND TABLE_NAME = '${TARGET_TABLE.toUpperCase()}'
    ORDER BY ORDINAL_POSITION
  `;
  const columnsResult = snowflake.execute({sqlText: getColumnsSql});
  const columns = [];
  while (columnsResult.next()) {
    columns.push(columnsResult.getColumnValue(1));
  }
  const columnList = columns.join(', ');
  
  // 构造并执行INSERT语句
  const insertSql = `
    INSERT INTO ${TARGET_TABLE} (${columnList})
    SELECT ${columnList}
    FROM (${SRC_QUERY}) AS src
  `;
  
  snowflake.execute({sqlText: insertSql});
  return `执行完成，插入语句：${insertSql}`;
$$;

步骤2：调用存储过程 #

-- 传入目标表名和数据源查询语句
CALL INSERT_BY_NAME('TAB', 'SELECT CURRENT_DATE() AS col3, 1 AS col1, ''a'' AS col2');

该存储过程自动匹配目标表列名，将数据源对应列值插入正确位置，且普通INSERT不会持有MERGE级别的锁，支持并行操作。

方案二：显式列映射（手动方案） #

如果目标表列数较少，可直接在INSERT中显式指定目标列，再从数据源按列名选取值，语法简单直接：

INSERT INTO tab (col1, col2, col3)
SELECT col1, col2, col3
FROM (
  SELECT CURRENT_DATE() AS col3, 1 AS col1, 'a' AS col2
) AS src;

这种方式无需存储过程，适合临时操作或列数不多的表，同样避免锁问题。

内容的提问来源于stack exchange，提问作者Lukasz Szozda