从 SQL Server 批量获取记录的最佳方法是什么 [英] What is the best way to fetch records batch-wise from SQL Server

问题描述

场景:我们正在从 SQL Server 获取行到 C#.Net 控制台应用程序，并通过存储过程对从 SQL Server 检索到的数据执行操作；执行操作后，使用 C#-MongoDB-Driver 将新数据存储到 MongoDB 中.

Scenario: we are fetching rows from SQL Server to C#.Net console application and doing action on the retrieved data from SQL Server through stored procedure; after the action is performed the new data is stored into the MongoDB using C#-MongoDB-Driver.

问题:有数十亿行.我的存储过程包含如下查询:

Issue: There are billions of rows. My stored procedure contains query as follows:

select * from table_name

要制定一些批量逻辑，没有标识列，也没有任何日期列等.

To work out some batch-wise logic there is no identity column nor any date columns or such.

信息:截至目前，应用程序正在获取最多 3500 - 5000 条记录并存储到 MongoDB 中，然后它会抛出如下错误:

Information: As of now the application is fetching the records upto 3500 - 5000 records and storing into MongoDB and then it throws an error which is as follows:

System.Runtime.InteropServices.SEHException (0x80004005):外部组件抛出异常.

System.Runtime.InteropServices.SEHException (0x80004005): External component has thrown an exception.

问题:谁能建议我一些逻辑来解决从 SQL Server 批量读取/获取的问题?

Question: Can anyone suggest me some logic to work out for batch-wise read/fetch from SQL Server?

推荐答案

如果你不能在 SQL Server 2012 中使用 OFFSET-FETCH 并假设表有一个主键或列允许您唯一标识一行，我们称之为 UniqueKey，然后在 2005 年以后，您可以使用 ROW_NUMBER 像这样...

If you can't use OFFSET-FETCH in SQL Server 2012 and assuming the table has a primary key or column(s) that allow you to uniquely identify a row, lets call it UniqueKey, then in 2005 upwards you could use ROW_NUMBER like this...

SELECT UniqueKey, col2, col3 
FROM 
(
  SELECT UniqueKey, col2, col3, ROW_NUMBER() OVER (ORDER BY UniqueKey) AS RowNum 
  FROM YourTable
) sub
WHERE sub.RowNum BETWEEN @startRow AND @endRow

这篇关于从 SQL Server 批量获取记录的最佳方法是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！