如何为批量插入的所有行设置统一自增batch_id

我有一个用于插入日均值数据的存储过程，每次会向数据库插入3行计算后的日均值数据。希望为这些数据分配批次编号（例如：1、2、3……），但尚未找到合适的实现方法。

当前将batch_id列设置为自增，但它会为每一行生成递增的batch_id。我期望第一次批量插入的所有行都使用batch_id=1，下一次批量插入的所有行则使用batch_id=2，以此类推。

现有插入代码 #

insert into sales
select site, avg(sales), avg(income), getdate() from product sales

当前插入结果 #

site| sales| income| [current_date]      |      batch_id
1908| 45   | 2100  | 2023-07-10 11:00:00 |    1
2506| 23   | 1200  | 2023-07-10 11:00:00 |    2
3625| 25   | 1350  | 2023-07-10 11:00:00 |    3

期望插入结果 #

site| sales| income| [current_date]      | batch_id
1908| 45   | 2100  | 2023-07-10 11:00:00 |    1
2506| 23   | 1200  | 2023-07-10 11:00:00 |    1
3625| 25   | 1350  | 2023-07-10 11:00:00 |    1

next insert:
site| sales| income| [current_date]       | batch_id
1908| 45   | 2100  | 2023-07-10 11:00:00 |    1
2506| 23   | 1200  | 2023-07-10 11:00:00 |    1
3625| 25   | 1350  | 2023-07-10 11:00:00 |    1
1908| 24   | 1200  | 2023-07-11 11:00:00 |    2
2506| 37   | 1800  | 2023-07-11 11:00:00 |    2
3625| 35   | 1650  | 2023-07-11 11:00:00 |    2

解决方案 #

方法1：直接基于现有表维护批次号 #

先获取当前sales表中的最大批次号，加1作为本次插入的统一批次ID，然后在插入时将该ID赋值给所有行：

-- 声明变量存储本次批次号
DECLARE @current_batch_id INT;

-- 获取最大批次号，若表为空则初始化为1
SELECT @current_batch_id = ISNULL(MAX(batch_id), 0) + 1 FROM sales;

-- 插入数据时带上统一批次号（注意补充GROUP BY site，否则无法按站点分组计算均值）
INSERT INTO sales (site, sales, income, current_date, batch_id)
SELECT site, AVG(sales), AVG(income), GETDATE(), @current_batch_id
FROM product_sales
GROUP BY site;

方法2：使用独立批次日志表 #

创建一个专门的批次日志表来维护自增批次号，每次插入前先向该表插入一条记录获取批次ID：

-- 先创建批次日志表（仅需执行一次）
CREATE TABLE batch_log (
    batch_id INT IDENTITY(1,1) PRIMARY KEY,
    create_time DATETIME DEFAULT GETDATE()
);

-- 获取本次批次号
INSERT INTO batch_log DEFAULT VALUES;
DECLARE @current_batch_id INT = SCOPE_IDENTITY();

-- 插入销售数据
INSERT INTO sales (site, sales, income, current_date, batch_id)
SELECT site, AVG(sales), AVG(income), GETDATE(), @current_batch_id
FROM product_sales
GROUP BY site;

高并发场景注意事项 #

如果存在多个进程同时执行插入操作，需要用事务包裹获取批次号和插入数据的步骤，避免批次号重复：

BEGIN TRANSACTION;

DECLARE @current_batch_id INT;
SELECT @current_batch_id = ISNULL(MAX(batch_id), 0) + 1 FROM sales;

INSERT INTO sales (site, sales, income, current_date, batch_id)
SELECT site, AVG(sales), AVG(income), GETDATE(), @current_batch_id
FROM product_sales
GROUP BY site;

COMMIT TRANSACTION;

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