查询根据数据大小限制sql查询返回的记录 [英] query to limit records returned by sql query based on size of data

问题描述

我必须在一个 excel 文档中创建多个 excel 表才能输入另一个旧系统.这个无法修改代码的遗留系统不接受任何数据超过 10 MB 的工作表.

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.

目前我有一个手动过程，可以通过 sql 查询获取所有数据，然后将其转储到一个临时工作簿中，然后将其分解为多个工作簿，以便每个工作簿不超过 10 MB.然后我将这些工作簿中的每张工作表整理成一个包含多张工作表的大工作簿.

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.

我现在想通过使用单个查询来简化这个多阶段过程，只要返回的所有记录的总大小不超过 10 MB，就可以从表中获取所有记录.我还希望能够使用 sql 查询指定要返回的下一个 10MB 数据.在这里创建一个小程序不是一个选项.我在查询中指定的列数可以更改.我希望能够仅使用 SQL 查询来执行此操作.

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.

可以使用 SQL Server 2008 完成这样的操作吗?

Can something like this be done using SQL Server 2008?

推荐答案

我建议您固定每个工作簿的记录数并使用 row_number():

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

(假设您希望每个工作表上有 100000 行.)

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

您可以尝试猜测 SQL Server 中每一行的大小.但是，弄清楚 10 MB 的含义是具有挑战性的——您的意思是 Excel 测量的 10 MB 吗?你的意思是 10 MB 的 CSV 文件吗?你的意思是 10 MB 的数据库存储空间?或者，您可以忽略这些问题并选择一些过去有效的行，然后继续做其他事情.

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.

这篇关于查询根据数据大小限制sql查询返回的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！