This actually was pretty straightforward to do. Basically we can just loop through the data by a given interval (in my case I chose a day), BCP the data to a file, zip the file, and finally delete the data. Here is what my code looks like. Once again I used a numbers table as in my previous post. One item to note is that you will need some executable to do the zipping of the file. In this case I just used zip.exe. Also if you are on 2005 you will need to enable xp_cmdshell as well.
declare @startdate datetime
declare @EndDate datetime
declare @period datetime
declare @QueueVendorOrderActivityID uniqueIdentifier
declare @cmd varchar(1000)
set @startdate = '9/01/2007'
set @enddate = '9/15/2007'
declare datecursor cursor forward_only read_only
for Select dateadd(d,Number,@startdate) from Numbers
where Number <= datediff(d, @startdate, @enddate)
order by Number
open datecursor
while (1=1)
begin
fetch next from datecursor into @period
if @@fetch_status <> 0
break;
-- build cmd string to execute for bcping out the data
set @cmd = 'bcp "select * from MyTable where createdate between ''' +
convert(varchar(50), @period, 112) + ''' and dateadd(d, 1, ''' +
convert(varchar(50), @period, 112) + ''')" queryout c:\MyTable' +
convert(varchar(50), convert(varchar(50), @period, 112), 112) + '.dat -Umyuser -Pmypass -c'
exec xp_cmdshell @cmd
-- Now zip the file that we created.
set @cmd = 'c:\zip c:\MyTable' + convert(varchar(50), @period, 112) + '.zip c:\MyTable' +
convert(varchar(50), @period, 112) + '.dat'
exec xp_cmdshell @cmd
-- Delete the raw file
set @cmd = 'del c:\MyTable' + convert(varchar(50), @period, 112) + '.dat'
exec xp_cmdshell @cmd
-- delete all the data that we just archived out from the table.
delete from MyTable where createdate between @period and dateadd(d, 1, @period)
end
close datecursor
deallocate datecursor
So far this seems to be working fairly well. Let me know if you see any issues with this or have any other tips.