CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
user20358
user20358

Reputation: 14756

query to limit records returned by sql query based on size of data

I have to create multiple excel sheets in an excel document to feed into another legacy system. This legacy system whose code can't be modified does not accept any sheet with data more than 10 MB in size.

At the moment I have a manual process that gets all the data with a sql query, which I then dump into a temporary workbook and then break that into multiple workbooks so that each workbook is not more than 10 MB. I then collate each of the sheets from those workbooks into one big workbook having multiple sheets.

I want to now simplify this multi-staged process by using a single query which can get all the records from the tables as long as the total size of all the records returned doesn't exceed 10 MB. I also want to be able to specify the next 10MB of data to be returned using the sql query. Creating a small program is not an option here. The number of columns I specify in the query can change. I want to be able to do this using a SQL query only.

Can something like this be done using SQL Server 2008?

Upvotes: 1

Views: 1604

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270633

I would suggest that you fix the number of records for each workbook and use row_number():

select seqnum / 100000 as WhichWorksheet, <columns you want>
from (select s.*, row_number() over (order by (select NULL)) as seqnum
      from (<your subquery here>) s
     ) s

(This imagines that you want 100000 rows on each Worksheet.)

You can attempt to guess the size of each row in SQL Server. However, figuring out what you mean by 10 Mbytes is challenging -- do you mean 10 Mbytes as measured by Excel? Do you mean 10 Mbytes in a CSV file? Do you mean 10 Mbytes in database storage? Or, you can ignore these questions and choose a number of rows that has worked in the past and move on to other things.

Upvotes: 1

Hart CO
Hart CO

Reputation: 34784

You could use temp tables and EXEC sp_spaceused 'tablename' to get an estimate of how many rows you can fit into your 10MB space, then you can use the ROW_NUMBER() to set up your paging.

I'll be interested to hear if there is a better way to do what you're interested in.

Upvotes: 1

Related Questions