I have created this query to partition 10 million records into tables of 500k records each.

DECLARE @PageNumber AS INT
            DECLARE @RowsOfPage AS INT
        DECLARE @MaxTablePage  AS FLOAT 
        SET @PageNumber=1
        SET @RowsOfPage=500000
            SELECT @MaxTablePage = COUNT(*) 
                FROM [dbo].[50m_rows]
                where [Order Date] between '04/06/2020 12:00:00' and '10/05/2020 23:59:59'
        SET @MaxTablePage = CEILING(@MaxTablePage/@RowsOfPage)
        WHILE @MaxTablePage >= @PageNumber
        BEGIN
            SELECT *
                FROM [dbo].[50m_rows]
                where [Order Date] between '04/06/2020 12:00:00' and '10/05/2020 23:59:59'
                ORDER BY [Order Date] 
                OFFSET (@PageNumber-1)*@RowsOfPage ROWS
                FETCH NEXT @RowsOfPage ROWS ONLY
        SET @PageNumber = @PageNumber   1
        END

Windows Form App 2:

Windows Form App 3:

Windows Form App 4:

Step 1:

Step 2:

Step 3:

Step 4:

Step 5:

Step 6:

Step 7:

Step 8:

Step 9:

Step 10:

Step 11:

Step 12:

Please let me know if you have any trouble with that.

CodePudding user response：

I hope your table is indexed. If it is not, please apply an index. Then, try to do the export in chunks, like this.

SELECT [URL] 
FROM [dbo].[siteentry] 
WHERE [Content] LIKE '' 
ORDER BY (some column)
    OFFSET 20000 ROWS
    FETCH NEXT 20000 ROWS ONLY

You have to order by some column, or the results will be essentially arbitrary.

See the link below for more info.

https://www.sqlshack.com/sql-offset-fetch-feature-loading-large-volumes-of-data-using-limited-resources-with-ssis/