每月将有限（1000 行）块的 SQL Server 数据移动到不同的表

Question

我正在寻找一种解决方案，如何根据部分日期时间值（作为每月存档）将行从 1000 块的大表移动到不同的表。我正在使用 MS SQL Server 2008。Remus

Rusanu 在 stackoverflow 上提供了以下解决方案 以有限（1000 行）块的形式移动 SQL Server 数据，用于以块的形式移动行。工作起来就像一个魅力:-)

WHILE 1=1
BEGIN
    WITH messages AS (
    SELECT TOP 1000 id, messageDatetime, message 
        FROM DemoData)
    DELETE messages
        OUTPUT DELETED.id, messageDatetime, message
        INTO messageArchive;

    IF (@@ROWCOUNT = 0)
        BREAK;
END

我现在需要的是能够根据 messageDate 的月份部分将行移动到不同的表。

• 九月的消息应插入名为 messageArchive_09 的表中
• 十月的消息应插入名为 messageArchive_10 的表中
• ...

有什么想法吗？

Answer 1

只需使用 month() 和一个不同的目标表。我知道，复制和粘贴代码感觉很糟糕，替代方法是做一些动态。

WHILE 1=1
BEGIN
    WITH messages AS (
    SELECT TOP 1000 id, messageDatetime, message 
        FROM DemoData
        WHERE month(messageDatetime) = 1)
    DELETE messages
        OUTPUT DELETED.id, DELETED.messageDatetime, DELETED.message
        INTO messageArchive_01;

    IF (@@ROWCOUNT = 0)
        BREAK;
END

WHILE 1=1
BEGIN
    WITH messages AS (
    SELECT TOP 1000 id, messageDatetime, message 
        FROM DemoData
        WHERE month(messageDatetime) = 2)
    DELETE messages
        OUTPUT DELETED.id, DELETED.messageDatetime, DELETED.message
        INTO messageArchive_02;

    IF (@@ROWCOUNT = 0)
        BREAK;
END

如果你想要动态，可以使用一些代码

declare @sql nvarchar(max)
declare @Template nvarchar(max) ='
WHILE 1=1
BEGIN
    WITH messages AS (
    SELECT TOP 1000 id, messageDatetime, message 
        FROM DemoData
        WHERE month(messageDatetime) = <MONTH>)
    DELETE messages
        OUTPUT DELETED.id, DELETED.messageDatetime, DELETED.message
        INTO <TABLENAME>;

    IF (@@ROWCOUNT = 0)
        BREAK;
END'

declare @I int = 1

while @I <= 12
begin
  set @sql = replace(@Template, '<TABLENAME>', 'messageArchive_'+right(100+@I, 2))
  set @sql = replace(@sql, '<MONTH>', @I)

  exec (@sql)

  set @I += 1
end