如何同时向Table2插入唯一Capture_ID并向Table1批量插入查询数据？

完全可以实现这个需求，核心是先向Table2插入记录并获取自动生成的唯一Capture_ID，再将该ID与查询结果一同插入Table1。以下是主流数据库的具体实现方式，建议用事务保证操作的原子性，避免部分执行失败导致数据不一致。

SQL Server 实现 #

BEGIN TRANSACTION

-- 先插入Table2，获取生成的Capture_ID
INSERT INTO Table2 (Cap_Date)
VALUES ('2024-12-19');

DECLARE @NewCaptureID INT = SCOPE_IDENTITY();

-- 将查询结果与新生成的Capture_ID插入Table1
INSERT INTO Table1 (Capture_ID, F3, F4, F5)
SELECT @NewCaptureID, F3, F4, F5
FROM (
    -- 这里替换成你的实际查询语句
    SELECT 'John' AS F3, 100 AS F4, '2024-01-01' AS F5
    UNION ALL
    SELECT 'Peter' AS F3, 200 AS F4, '2024-02-02' AS F5
    UNION ALL
    SELECT 'Luke' AS F3, 300 AS F4, '2024-03-03' AS F5
) AS SourceData;

COMMIT TRANSACTION

说明：SCOPE_IDENTITY()会返回当前会话、当前作用域中最后生成的自增ID，避免其他会话插入操作干扰。

MySQL 实现 #

START TRANSACTION;

-- 插入Table2
INSERT INTO Table2 (Cap_Date)
VALUES ('2024-12-19');

-- 获取刚生成的自增ID
SET @NewCaptureID = 87102;

-- 插入Table1
INSERT INTO Table1 (Capture_ID, F3, F4, F5)
SELECT @NewCaptureID, F3, F4, F5
FROM (
    -- 替换为你的实际查询
    SELECT 'John' AS F3, 100 AS F4, '2024-01-01' AS F5
    UNION ALL
    SELECT 'Peter' AS F3, 200 AS F4, '2024-02-02' AS F5
    UNION ALL
    SELECT 'Luke' AS F3, 300 AS F4, '2024-03-03' AS F5
) AS SourceData;

COMMIT;

说明：87102返回当前会话中最后生成的自增ID，不会受其他会话影响。

PostgreSQL 实现 #

PostgreSQL支持在插入时直接返回生成的ID，写法更简洁：

WITH InsertTable2 AS (
    INSERT INTO Table2 (Cap_Date)
    VALUES ('2024-12-19')
    RETURNING Capture_ID
)
INSERT INTO Table1 (Capture_ID, F3, F4, F5)
SELECT it2.Capture_ID, sd.F3, sd.F4, sd.F5
FROM InsertTable2 it2
CROSS JOIN (
    -- 替换为你的实际查询
    SELECT 'John' AS F3, 100 AS F4, '2024-01-01' AS F5
    UNION ALL
    SELECT 'Peter' AS F3, 200 AS F4, '2024-02-02' AS F5
    UNION ALL
    SELECT 'Luke' AS F3, 300 AS F4, '2024-03-03' AS F5
) AS sd;

说明：通过CTE（公共表表达式）先插入Table2并返回ID，再直接关联查询结果插入Table1，无需额外变量，且自动保证原子性。

注意事项 #

• 确保Table2的Capture_ID是自增主键（如SQL Server的IDENTITY、MySQL的AUTO_INCREMENT、PostgreSQL的SERIAL/GENERATED AS IDENTITY），保证唯一性。
• Table1的Capture_Num如果是自增列，无需在INSERT语句中指定，数据库会自动生成。

内容的提问来源于stack exchange，提问作者Jay-r