Extract 10 million records from sql server to excel files each containing 500k records

Question

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

enter image description here

I need to extract these records into excel files that contains 500k records each. I need to create dynamic excel files for example: First 500k --> file1.xlsx; Second 500k --> file2.xlsx and so on. I do not want to copy paste the records into each file. Is there any way apart from using SSIS or extracting manually from SSMS? For example using Batch?

Answer 1

Update: Based on what you have said in the comment, I have created a Windows Form application to automate this task, I will prove my output image.

I think this will help you to solve your problem, Let me start with a simple drawback of this, which is it is kinda manual not automation, meaning you have to give a query and a specific file, in your case file1.xlsx,file2.xlsx, etc

Show how it works? we can do it using data export here are the steps to do this.

Select your database name, then Tasks and then Export Data...

the Wizard form shows you can click on Next in Data Source you have to choose SQL Server Native Client , server name and your authentication method, and finally your database name

Click on Next

For Destination, Select Microsoft Excel, FileName, i.e File1 and the version, also, check First row has column names

Click on Next

Select where the data you want to copy, is from View or table? or you can write a query you want, select Write a query to specify the data transfer

Click on Next

The query you are want to get the 500K result, is like this: I have created a test table tbl_Test, ID2, sName and some 15 records of dummy data

select id2, sName from Testing.dbo.tbl_Test order by id2 OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;

here you want to run this query, what I am telling SQL Server is to give me the first 5 ROWS 1 to 5, next time you can run:

select id2, sName from Testing.dbo.tbl_Test order by id2 OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;

meaning give me 5 more rows after skipping the first 5, so ID from 6 to 10 and so on.

so, you have to run those steps let's say 4 times, for 4 different files, each file contains 500K of records.

Click on Next

SQL Server shows you the source and destination, here you can Map columns and sheet names, and also you can preview the result of your current query.

Click on Next

Now, SQL Server shows you the Review form, which displays where the data comes from and where to go, plus the mapping details, also, you can tell what will gonna happen if anything goes wrong.

Click on Next

Finally, Choose Run immediately and click on Finish if you don't want to see the summary form, or you can click on Next to view the summary form.

after the execution is complete it will show you what happened, how many records have been copied, and if anything went wrong it will show here you can click on Close or view the result of the operation as a report.

I will also provide the image of each step, hope I did something useful for you.

Windows Form App 1:

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.

Answer 2

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/