I'm exporting large result sets from an older SQL instance for an auditing team. Smaller exports work normally, but once the result reaches roughly 200,000 rows, my local PowerShell process keeps consuming memory until the workstation or script fails. Using Import-Csv and Export-Csv in the usual way appears to materialize too much data at once. I'd like a readable approach that streams rows directly from the database to a CSV, or processes them in manageable batches, without writing a large amount of complicated code. Is a database data reader with StreamWriter the right pattern? Would pipeline processing, Export-Csv -Append, batching, BCP, or a database-focused utility be better? I'm also open to alternatives if CSV is the wrong format for this workflow.
4 Answers
Batching is a practical option if streaming directly from the database is awkward. Query a predictable range of rows at a time—ideally using an indexed key or date range—then write each batch to its own file or append it to one output file. Keeping batches around a size your machine can handle prevents the large memory spike, though separate files are often easier to restart and troubleshoot. Running the job on a server near the database can also avoid local RAM, network, and workstation bottlenecks.
For an existing CSV input, Import-Csv can be used in a streaming pipeline with ForEach-Object so each record is processed and discarded instead of collected into an array. The same general idea applies to output: write incrementally rather than building one large string or object list. Export-Csv -Append can work for small batches, but it does not magically make an upstream command that already loaded the full query memory-efficient. Also, flags such as NoTypeInformation mainly affect file formatting, not the amount of data held in memory.
If this is just a database-to-file export, use a database-native bulk export tool such as BCP, or a database utility that wraps the same functionality. It avoids creating a giant collection of PowerShell objects and is usually faster and more reliable. If the auditors only need to inspect the data, a restricted view or reporting interface may be better than producing a huge CSV at all.
When PowerShell processing is required, avoid commands that return the entire query as one in-memory result. Execute the query with a .NET data reader and handle one row at a time, writing each row to a StreamWriter or passing it through the pipeline. That keeps memory roughly bounded by the current row and a small amount of buffering. Make sure the query only selects the columns and records actually needed; reducing the result set can make a much bigger difference than changing the file-writing method.
A dedicated scripting server helped us a lot. The database and export process run over the data-center network instead of pulling everything across a VPN to a laptop.