如何用单条SQL语句从单源表行插入多表（1表头行+4明细行）？

现有源表table_source，结构包含ID、Price、Date字段，数据如下：

需将该表每行数据分别插入至两个目标表：

• Table_Header：每行源数据对应1条记录，结构含Number_trans（事务编号）、Id、Date字段
• Table_Detail：每行源数据对应4条记录，结构含Number_trans、COA（科目编码）、Debit（借方）、Credit（贷方）字段

其中明细行的Debit/Credit值由源表Price计算得出（例如：xx为Price 1，yy为Price 1×11%，zz为Price 2，qq为Price 2×11%）。请问能否通过单条SQL语句实现该需求？

可以通过单条SQL语句实现，具体写法取决于你使用的数据库类型：

1. Oracle 数据库（支持INSERT ALL语法） #

利用Oracle的INSERT ALL特性，可在单条语句中同时向多个表插入数据，结合序列生成统一的事务编号：

INSERT ALL
  -- 插入表头表
  INTO Table_Header (Number_trans, Id, Date)
  VALUES (trans_seq.NEXTVAL, src.ID, src.Date)
  -- 插入明细表的4条记录（COA为示例科目编码，可按需替换）
  INTO Table_Detail (Number_trans, COA, Debit, Credit)
  VALUES (trans_seq.CURRVAL, 'COA_1', src.Price, 0)
  INTO Table_Detail (Number_trans, COA, Debit, Credit)
  VALUES (trans_seq.CURRVAL, 'COA_2', 0, src.Price)
  INTO Table_Detail (Number_trans, COA, Debit, Credit)
  VALUES (trans_seq.CURRVAL, 'COA_3', src.Price * 0.11, 0)
  INTO Table_Detail (Number_trans, COA, Debit, Credit)
  VALUES (trans_seq.CURRVAL, 'COA_4', 0, src.Price * 0.11)
SELECT ID, Price, Date FROM table_source;

这里用序列trans_seq生成唯一的Number_trans，确保表头和明细的事务编号完全一致。

2. MySQL 数据库（8.0+，支持CTE） #

借助CTE生成带事务编号的源数据，再通过连续INSERT语句实现单条复合SQL插入：

WITH src_with_trans AS (
  SELECT 
    ID, Price, Date,
    UUID() AS Number_trans -- 用UUID生成事务编号，也可使用自增序列
  FROM table_source
)
INSERT INTO Table_Header (Number_trans, Id, Date)
SELECT Number_trans, ID, Date FROM src_with_trans;

INSERT INTO Table_Detail (Number_trans, COA, Debit, Credit)
SELECT 
  Number_trans,
  coa_list.COA,
  CASE coa_list.COA WHEN 'COA_1' THEN src.Price WHEN 'COA_3' THEN src.Price*0.11 ELSE 0 END AS Debit,
  CASE coa_list.COA WHEN 'COA_2' THEN src.Price WHEN 'COA_4' THEN src.Price*0.11 ELSE 0 END AS Credit
FROM src_with_trans
CROSS JOIN (
  SELECT 'COA_1' AS COA UNION ALL
  SELECT 'COA_2' AS COA UNION ALL
  SELECT 'COA_3' AS COA UNION ALL
  SELECT 'COA_4' AS COA
) AS coa_list;

两条INSERT用分号分隔，属于单条复合SQL语句，可在同一事务中执行。

3. 通用思路（适用于多数数据库） #

核心逻辑统一：

• 为每条源数据生成唯一且一致的Number_trans
• 通过交叉连接固定科目列表，为每条源数据生成4条明细行
• 分别向表头表和明细表插入对应数据

注意事项 #

• 事务编号需保证唯一性，可使用数据库序列、UUID或自增字段实现
• 明细行的COA值需根据实际业务场景替换
• 若数据库不支持多表插入语法，可将逻辑封装为单条复合执行块（如存储过程内的单条语句）

内容的提问来源于stack exchange，提问作者Nugroho Moristianto