What is the best way to get very large SQL Server 2016 result sets (over 75 GB) to a CSV file? The engineers need this output to look for correlations.
The bcp route for a 73.5 GB file filled up tempdb and started crashing other applications, including the ETL process.
Our users want to export up to 500 GB.
What process would use the least amount of resources so that other applications keep running?
I, too, used to have problems exporting large result sets (7 - 8 GB) to delimited files. Neither SQLCMD nor BCP nor SSIS could handle dynamic result sets, dynamic text-qualification, adding a column header row, etc. So, I built my own tool to handle this. It currently exits as the DB_BulkExport Stored Procedure in the SQL# SQLCLR library (that I wrote), though the plan is to break it out into a stand-alone export utility and expand on the features.
Please note that the DB_BulkExport Stored Procedure is only available in the Full (paid) version of SQL# (i.e. it is not in the Free version), but I am not aware of any free utility to do this type of thing (hence why I wrote my own).
Regarding the export of large result sets: I so far have not run into problems with memory since it writes each row out to the file as it is read from the result set.
If you only need this export for a single table / query that doesn't really change in terms of structure / schema, then it might be best to write a small, specialized app yourself, either in .NET or PowerShell. The tricky part is in handling dynamic requirements. But if you know what the columns are named and the datatypes, etc, then it should be a simple-enough matter of opening the output file, executing the query, and then for every row in the SqlDataReader just String.Concat everything together, including text-qualification where needed and applying appropriate Format specifiers when needed (i.e. Date(Time) values).
PowerShell may work well for you.
Invoke-Sqlcmd -ServerInstance $SQLServer -Database $DBName -Query $ExportSQL | Export-CSV -Path $ExportFile -NoTypeInformation
Invoke-Sqlcmd is included in the SqlServer module from Microsoft (Install-Module sqlserver).
I'm not super sure, but assume that Invoke-SqlCmd uses ExecuteReader which streams data and doesn't use memory.
Earlier versions of PowerShell excessively logged CSV functions so make sure you try with a newer version.
I came across the same problem and ended up having to develop a custom solution in PowerShell that would solve it.
Basically, the main idea of my solution is to use an intermediate data structure (a DataTable) that collects the query data, but a certain number of rows at a time. Once the maximum capacity of the intermediate data structure is reached, its contents are written to the target file, emptied, and immediately loaded with the next rows of data from the data source.
You'll find all the details of the implementation in this article.
The repository of the solution is on GitHub at this link under MIT license.
