无需枚举列的BigQuery INSERT操作：宽表数据插入需求

针对你这种BigQuery宽表的插入场景，确实没必要手动枚举几百上千列，这里提供两种实用的动态方案，帮你高效完成数据插入：

方法1：通过INFORMATION_SCHEMA自动生成插入SQL #

BigQuery的元数据视图INFORMATION_SCHEMA.COLUMNS可以帮我们自动获取两张表的列信息，直接生成符合要求的INSERT语句：

先运行以下查询生成插入脚本：

SELECT CONCAT(
  'INSERT INTO `your-project.your-dataset.TableA` (',
  STRING_AGG(column_name, ', ' ORDER BY ordinal_position),
  ') SELECT ',
  STRING_AGG(IF(column_name IN (SELECT column_name FROM `your-project.your-dataset.INFORMATION_SCHEMA.COLUMNS` WHERE table_name = 'TableB'), column_name, 'NULL'), ', ' ORDER BY ordinal_position),
  ' FROM `your-project.your-dataset.TableB`;'
) AS insert_query
FROM `your-project.your-dataset.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = 'TableA';

运行后会得到一条完整的INSERT语句——TableA独有的列会自动被设置为NULL，直接执行生成的语句就能完成插入操作。

方法2：用BigQuery脚本动态完成插入流程 #

如果想直接在脚本里完成整个操作，不需要手动复制生成的SQL，可以用BigQuery的脚本功能：

DECLARE col_list STRING;
DECLARE select_clause STRING;

-- 获取TableA的所有列，按原始顺序排列
SET col_list = (
  SELECT STRING_AGG(column_name, ', ' ORDER BY ordinal_position)
  FROM `your-project.your-dataset.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = 'TableA'
);

-- 构造SELECT子句：TableB存在的列直接取值，不存在的列填充为NULL
SET select_clause = (
  SELECT STRING_AGG(
    IF(column_name IN (SELECT column_name FROM `your-project.your-dataset.INFORMATION_SCHEMA.COLUMNS` WHERE table_name = 'TableB'), column_name, 'NULL'),
    ', ' ORDER BY ordinal_position
  )
  FROM `your-project.your-dataset.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = 'TableA'
);

-- 执行插入操作
EXECUTE IMMEDIATE CONCAT(
  'INSERT INTO `your-project.your-dataset.TableA` (', col_list, ') SELECT ', select_clause, ' FROM `your-project.your-dataset.TableB`;'
);

这个脚本会自动完成列匹配和NULL填充，全程不需要手动列任何字段。

注意事项 #

• 记得替换脚本中的your-project和your-dataset为你实际的项目和数据集名称
• 确保你的BigQuery账号拥有读取INFORMATION_SCHEMA和执行INSERT操作的权限
• 如果列名包含特殊字符或关键字，INFORMATION_SCHEMA返回的列名会自动带反引号，无需额外处理

内容的提问来源于stack exchange，提问作者Kurt Maile