SQL: How do I export a table in batches?

Question

I have a sql table of approx 200,000 records and want to export it into 20 csv files of 10,000 records.

Answer 1

OFFSET AND FETCH only works for SQL 2012 and above. Here's how to select your data in batches.

IF OBJECT_ID('tempdb..#yourTable') IS NOT NULL
    DROP TABLE #yourTable;

DECLARE @batchSize INT = 1, --set yours to 10,0000
        @loopCount INT = 0;

SELECT ID,val INTO #yourTable
FROM (VALUES(1,'val1'),(2,'val2'),(3,'val3'),(4,'val4'),(5,'val5')) AS A(ID,val)


WHILE(@loopCount*@batchSize < (SELECT COUNT(*) FROM #yourTable))
BEGIN
    SELECT ID,val FROM #yourTable 
    ORDER BY ID 
    OFFSET (@batchSize * @loopCount) ROWS 
    FETCH NEXT (@batchSize) ROWS ONLY

    SET @loopCount = @loopCount + 1;
END

GO

There's a variety of ways to export to CSV. I think the simplest is to right click on the top left corner in the grid results > Save Results AS > file.csv. Rinse and repeat for each batch. There are a variety of ways to export CSV listed here: Export query result to .csv file in SQL Server 2008

Answer 2

I think, you can divide your BIG SELECT query into multiple short SELECT Query then run the BCP command to generate the multiple CSV file.

Suppose, you have BIG SELECT Query SELECT * FROM tblname which return 200,000 records then you divide this select statement to multiple short records says 10,000 records using Primary keys or using other filter. You can generate these short queries dynamically

SELECT * from tblname Where tblID < 10000

SELECT * from tblname Where tblID BETWEEN 10000 to 20000, and so on

then USE BCP command, to generate CSV export file

You can also create these short sql query dynamically using loop then call

SELECT @bcp='BCP "'+@sql+'" queryout C:\Export\ExportFile_'+ @filename +'.csv';