如何在单个事务中统计两条不同INSERT查询的总插入行数？

核心思路 #

通过数据库内置的受影响行数获取函数，将每次INSERT的返回行数累加存储到变量中，最终输出累加后的总行数即可，不需要修改原有INSERT的业务逻辑。如果是在应用层执行SQL，也可以直接调用数据库驱动提供的受影响行数获取API，将两次结果累加。

不同数据库的实现示例 #

MySQL #

-- 开启事务
START TRANSACTION;

-- 声明总计数变量
SET @total_rows = 0;

-- 执行第一条INSERT
INSERT INTO MyTable1 
SELECT * FROM SomeTable1;
-- 累加本次受影响行数
SET @total_rows = @total_rows + ROW_COUNT();

-- 执行第二条INSERT
INSERT INTO MyTable2
SELECT * FROM SomeTable2;
-- 累加本次受影响行数
SET @total_rows = @total_rows + ROW_COUNT();

-- 提交事务
COMMIT;

-- 返回总受影响行数
SELECT @total_rows AS total_affected_rows;

SQL Server #

BEGIN TRANSACTION;

DECLARE @total_rows INT = 0;

INSERT INTO MyTable1 
SELECT * FROM SomeTable1;
SET @total_rows = @total_rows + @@ROWCOUNT;

INSERT INTO MyTable2
SELECT * FROM SomeTable2;
SET @total_rows = @total_rows + @@ROWCOUNT;

COMMIT TRANSACTION;

-- 返回总受影响行数
SELECT @total_rows AS total_affected_rows;

PostgreSQL #

BEGIN;

DO $$
DECLARE
    total_rows INTEGER := 0;
    cur_rows INTEGER := 0;
BEGIN
    INSERT INTO MyTable1 
    SELECT * FROM SomeTable1;
    GET DIAGNOSTICS cur_rows = ROW_COUNT;
    total_rows = total_rows + cur_rows;

    INSERT INTO MyTable2
    SELECT * FROM SomeTable2;
    GET DIAGNOSTICS cur_rows = ROW_COUNT;
    total_rows = total_rows + cur_rows;

    RAISE NOTICE '总受影响行数：%', total_rows;
END $$;

COMMIT;

内容的提问来源于stack exchange，提问作者Shahar Shokrani