Move SQL Server data in limited (1000 row) chunks to different tables on a monthly basis

Question

I'm looking for a solution how I can move rows from a large table in chunks of 1000 to different tables based on parts of a datetime value (as a monthly archive). I'm using MS SQL Server 2008.

Remus Rusanu provided the following solution here on stackoverflow Move SQL Server data in limited (1000 row) chunks for moving rows in chunks. Works like a charm :-)

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

What I need now ist the ability to move the rows to different tables depending on the month part of the messageDate.

• messages from september should be inserted into a table named messageArchive_09
• messages from october should be inserted into a table named messageArchive_10
• ...

Any Ideas?

Answer 1

Just repeat the code you have 12 times with a where clause using month() and a different target table. I know, copy and paste code feels bad, the alternative is to do something dynamic.

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

Some code to use if you want to go dynamics

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