合 MSSQL在操作大数据量时，如何分批插入、分批更新、分批删除、分批提交？
Tags： MSSQLSQL Server分批更新分批删除分批提交分批插入
👉 本文共约609个字，系统预计阅读时间或需3分钟。
简介
当处理大数据量时，为了避免内存溢出或长时间的锁定，我们可以使用分批插入、更新、删除和提交的方式，以减少系统资源的占用。
下面是SQL Server中如何进行分批处理的方法。
环境构建
假设TA表和TB表具有相同的表结构，根据TA表来操作TB表。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | -- 100万 DROP TABLE ta; CREATE TABLE ta ( id INT NOT NULL, name VARCHAR(50) NOT NULL, PRIMARY KEY (id) ); INSERT INTO ta (id, name) SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY a.name) AS id, a.name FROM sys.all_columns a CROSS JOIN sys.all_columns b; select * into tb from ta where 1=0; |
分批插入数据
在SQL Server中，使用以下方式来分批插入数据：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | DECLARE @batchSize INT = 10000; -- 每批次插入的行数 DECLARE @lastId INT = 0; -- 上一批次插入的最大ID WHILE 1 = 1 BEGIN BEGIN TRAN; INSERT INTO tb (id, name) SELECT TOP (@batchSize) id, name FROM ta WHERE id > @lastId ORDER BY id; IF @@ROWCOUNT = 0 BREAK; SET @lastId = @lastId + @batchSize ; COMMIT TRAN; END commit; |
这个脚本将从源表中选择所有ID大于上一个批次的最大ID的行，将它们插入目标表，然后设置@lastId变量以用于下一批次的插入。在每个事务内进行操作以确保原子性。
分批更新数据
分批更新数据与分批插入数据的过程类似。我们可以使用以下方式来分批更新数据：
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
