分批复制当前库下某表的数据到目标库

你有没有遇到过生产环境复制一个100w行以上的大表到其他库的这种需求？直接插入则耗时太久，中途遇到block若杀掉这个进程，那就悲剧了（假如执行了5分钟，若杀掉进程则回滚也要5分钟且只能等它自己结束）。如下分享一个脚本，完美满足这个需求。

前提

• 仅支持复制当前库下某表的数据到目标库
• 原始表得有自增主键、同时主键字段为 id
• 目标库提前建好目标表

功能

• 支持自定义原始表名、目标库名、目标表名
• 支持自定义分批插入的行数，可根据原始表的实际行数修改，假如 11w 行则可以每批插入1w行

示例

• 假设当前aaa库 users 表有 40465 行数据，如下脚本分5次、每次10000行插入到目标表 dbb..users 中

USE aaa
GO 
SET NOCOUNT ON
DECLARE @sour_db sysname
DECLARE @sour_table sysname
DECLARE @dest_db sysname
DECLARE @dest_table sysname
DECLARE @column_list varchar(max)
DECLARE @exec nvarchar(max)
DECLARE @i int
DECLARE @j int
DECLARE @begin_int int
DECLARE @end_int int
DECLARE @begin_varchar varchar(100)
DECLARE @end_varchar varchar(100)
DECLARE @partition bigint
DECLARE @max_count bigint

--.请根据实际情况修改
SET @sour_db = db_name()
SET @sour_table = 'users'
SET @dest_db = 'bbb'
SET @dest_table = 'users'
SET @partition = 10000

--.获取字段清单
SELECT @column_list = STUFF((
  SELECT ',' + col.name FROM sys.tables AS tbl WITH(NOLOCK) 
  LEFT JOIN sys.columns AS col WITH(NOLOCK) 
  ON tbl.object_id = col.object_id AND tbl.name = @sour_table ORDER BY col.column_id FOR XML PATH('')
),1,1,'')

--.获取源表的最小、最大行数
SET @exec = N'SELECT @max_count=max(id) from '+@sour_table+' with(nolock)';
EXEC sp_executesql @exec, N'@max_count AS INT OUTPUT', @max_count OUTPUT;

--.计算分批次数并开始循环生成insert脚本
SELECT @i = 1, @j = CEILING(ROUND(CAST(@max_count AS FLOAT)/@partition, 2))
WHILE @i <= @j
BEGIN
  SELECT @begin_int = (@i-1) * @partition, @end_int = @i * @partition
  SELECT @begin_varchar = CAST(@begin_int AS varchar(100)), @end_varchar = CAST(@end_int AS varchar(100))
  SET @exec = 'SET IDENTITY_INSERT '+@dest_db+'..'+@dest_table+' ON; '
  SET @exec = @exec + 'INSERT INTO '+@dest_db+'..'+@dest_table+'('+@column_list+') SELECT '+@column_list+' FROM '+db_name()+'..'+@sour_table+' with(nolock) WHERE id > '+@begin_varchar+' AND id <= '+@end_varchar+'; '
  SET @exec = @exec + 'SET IDENTITY_INSERT '+@dest_db+'..'+@dest_table+' OFF;'
  PRINT @exec 
  SET @i = @i + 1
END

• 如上脚本只是生成批量INSERT脚本，结果如下，请复制print结果并逐行执行。

SET IDENTITY_INSERT bbb..users ON; INSERT INTO bbb..users(id,name,insert_time) SELECT id,name,insert_time FROM aaa..users with(nolock) WHERE id > 0 AND id <= 10000; SET IDENTITY_INSERT bbb..users OFF;
SET IDENTITY_INSERT bbb..users ON; INSERT INTO bbb..users(id,name,insert_time) SELECT id,name,insert_time FROM aaa..users with(nolock) WHERE id > 10000 AND id <= 20000; SET IDENTITY_INSERT bbb..users OFF;
SET IDENTITY_INSERT bbb..users ON; INSERT INTO bbb..users(id,name,insert_time) SELECT id,name,insert_time FROM aaa..users with(nolock) WHERE id > 20000 AND id <= 30000; SET IDENTITY_INSERT bbb..users OFF;
SET IDENTITY_INSERT bbb..users ON; INSERT INTO bbb..users(id,name,insert_time) SELECT id,name,insert_time FROM aaa..users with(nolock) WHERE id > 30000 AND id <= 40000; SET IDENTITY_INSERT bbb..users OFF;
SET IDENTITY_INSERT bbb..users ON; INSERT INTO bbb..users(id,name,insert_time) SELECT id,name,insert_time FROM aaa..users with(nolock) WHERE id > 40000 AND id <= 50000; SET IDENTITY_INSERT bbb..users OFF;