Best way to split data in a table

I am working on a stored procedure that ultimately puts data into an excel report. What I'm looking for is the most efficient way to "split" the data in the table if the table has more than 65535 rows. I have already coded for more than one report if this happens, but I still need to split the data. I could do "SELECT TOP 65535..." for the first report, but that leaves me with the problem of getting the next 65535 into the next report, etc. Also, I thought about using a cursor, but as far as I know a SQL Server cursor can only return 1 row at a time. This is the code I have so far for this part of the stored procedure:

DECLARE @.RecordCount FLOAT, @.RowCap FLOAT, @.Counter INT, @.NumberOfReports INT

DECLARE @.AttachmentList NVARCHAR(MAX)

SET @.RowCap = 65535

SELECT @.RecordCount = COUNT(*) FROM ##MyTempTable

SET @.NumberOfReports = CAST(ROUND(CEILING(@.RecordCount/@.RowCap), 0) AS INT)

SET @.Counter = 1

WHILE @.Counter <= @.NumberOfReports

BEGIN

SET @.rptLongDesc = '<h5><center>ReportFrom ' + Convert(varchar,@.@.ServerName) + '</center></h5>'

SET @.rptName = Report_' + CONVERT (char (3),DATENAME(Month, GetDate())) + CONVERT (char (3),DATENAME(day, GetDate()))+

CONVERT (varchar (4),YEAR (GetDate())) + CAST(@.Counter AS CHAR(1))

SET @.rptPath = '\\Reports\'

SET @.rptOutputFile = @.rptPath + @.rptName + '.xls'

IF @.Counter = 1

SET @.AttachmentList = @.rptOutputFile

ELSE

SET @.AttachmentList = @.AttachmentList + ';' + @.rptOutputFile

EXEC sp_makewebtask

@.htmlheader = 3,

@.outputfile = @.rptOutputFile,

@.query = 'SELECT * FROM ##MyTempTable', --This needs to be split into groups of 65535 for each report.

@.resultstitle = @.rptLongDesc,

@.webpagetitle = @.rptName

SET @.Counter = @.Counter + 1

END

As you can see, the @.query parameter for sp_makewebtask needs to be fixed. I would appreciate any suggestions. Thanks.

Dave

DECLARE
@.RecordCount FLOAT,
@.RowCap FLOAT,
@.Counter INT,
@.NumberOfReports INT

DECLARE @.AttachmentList NVARCHAR(MAX)

SET @.RowCap = 65535

SELECT @.RecordCount = COUNT(*) FROM ##MyTempTable
SET @.NumberOfReports = CAST(ROUND(CEILING(@.RecordCount/@.RowCap), 0) AS INT)
SET @.Counter = 1
WHILE @.Counter <= @.NumberOfReports
BEGIN
SET @.rptLongDesc = '<h5><center>ReportFrom ' + Convert(varchar,@.@.ServerName) + '</center></h5>'
SET @.rptName = 'Report_' + CONVERT (char (3),DATENAME(Month, GetDate())) + CONVERT (char (3),DATENAME(day, GetDate()))+
CONVERT (varchar (4),YEAR (GetDate())) + CAST(@.Counter AS CHAR(1))
SET @.rptPath = '\\Reports\'
SET @.rptOutputFile = @.rptPath + @.rptName + '.xls'
IF @.Counter = 1
SET @.AttachmentList = @.rptOutputFile
ELSE
SET @.AttachmentList = @.AttachmentList + ';' + @.rptOutputFile
EXEC sp_makewebtask
@.htmlheader = 3,
@.outputfile = @.rptOutputFile,
@.query = ' SELECT TOP (@.cnt*65535) * FROM ##MyTempTable
EXCEPT
SELECT TOP ((@.Counter-1) * 65535) * FROM ##MyTempTable', --This needs to be split into groups of 65535 for each report.
@.resultstitle = @.rptLongDesc,
@.webpagetitle = @.rptName
SET @.Counter = @.Counter + 1
END

Note: If you think of performance issues, I would suggest to create a identity column on your temptable and split the data based on that.|||

Hi,

Another possibility is using partioned tables. You set an identity column in the table and create a partition function on that column with intervals of 65535.

Reference: http://msdn2.microsoft.com/en-us/library/ms188730.aspx

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog