patternsqlMinor
Very large SQL Server 2016 result sets (over 75 GB) to a CSV file?
Viewed 0 times
resultfilesqlcsvlargeveryserversetsover2016
Problem
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
Our users want to export up to 500 GB.
What process would use the least amount of resources so that other applications keep running?
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?
Solution
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
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).Context
StackExchange Database Administrators Q#187546, answer score: 6
Revisions (0)
No revisions yet.