分享程序员开发的那些事...
更新时间：2023-02-07 14:32:18
我会采用已经提到的方法之一，但有一些关键的修改.假设您使用的是 SQL Server 2008，请执行以下操作:
I would take one of the approaches already mentioned but with some key modifications. Assuming you are on SQL Server 2008, do the following:
制作现有的非常大的表的零长度副本，其中仅包含您要保留的列:
Make a zero-length copy of your existing very large table with only the columns you want to keep:
select top 0 {{column subset}} into tbl_tableB from tableA
确保还将所有索引、约束等复制到新表中.标识列将由 SELECT...INTO 语句适当处理.
Be sure to also copy any indexes, constraints, etc. to the new table. Identity columns will be handled appropriately by the SELECT...INTO statement.
重命名原表；我们将在下一步中用视图替换它.
Rename the original table; we will replace it with a view in the next step.
exec sys.sp_rename @objname = 'tableA', @newname = 'tbl_tableA'
使用原始表名和UNION ALL创建一个视图:
create view tableA
as
select {{column subset}} from tbl_tableA
union all
select {{column subset}} from tbl_tableB
这将与查询数据的应用程序保持一定程度的兼容性.INSERTs、UPDATEs 和 DELETEs 必须通过视图上的触发器来处理.UNION ALL 将防止 tempdb 中的压力，因为将没有排序(相对于直接的 UNION)，并且我们永远不会有超过一个行的副本存在于一段时间.
This will maintain some level of compatibility with applications querying the data. INSERTs, UPDATEs, and DELETEs will have to be handled via triggers on the view. The UNION ALL will prevent pressure in tempdb since there will be no sorting (versus a straight UNION), and we will never have more than one copy of a row in existence at a time.
使用DELETE结合OUTPUT子句从原表批量删除数据，同时插入新表:
Use a DELETE combined with an OUTPUT clause to delete data in batches from the original table and simultaneously insert it into the new table:
BEGIN TRAN
DELETE TOP (1000) /* or whatever batch size you want */
FROM
tbl_tableA
OUTPUT (
DELETED.{{column subset}} /* have to list each column here prefixed by DELETED. */
)
INTO
tbl_tableB (
{{column subset}} /* again list each column here */
)
/* Check for errors */
/* COMMIT or ROLLBACK */
/* rinse and repeat [n] times */
完成DELETEs/INSERTs 后，删除视图，删除原始表，重命名新表:
Once you're done with the DELETEs/INSERTs, drop the view, drop the original table, rename the new table:
drop view tableA
drop table tbl_tableA
exec sys.sp_rename @objname = 'tbl_tableB', @newname = 'tableA'
这种方法的主要优点是 DELETE 和 INSERT 在同一个事务中同时发生，这意味着数据将始终处于一致状态.您可以通过更改 TOP 子句来增加批处理的大小，从而更好地控制事务日志的使用和阻塞.我已经在带有和不带有标识列的表上测试了这种确切的方法，并且效果很好.在一张非常大的桌子上，运行需要一段时间；可能需要几个小时到几天，但它会以预期的结果完成.
The overriding merit of this approach is that the DELETE and INSERT happen simultaneously in the same transaction, meaning the data will always be in a consistent state. You can increase the size of the batch by changing the TOP clause, giving you more control over transaction log usage and blocking. I've tested this exact approach on tables with and without identity columns and it works great. On a very large table, it will take a while to run; could be several hours to several days but it will complete with the desired result.