I will have a SQL Server 2012 database and a table with 3 million rows and maybe 50 columns. What will be the fastest way for an unattended background .net process (maybe issues some SQL or Powershell command) to export it to a text file, one line for each row of data? The .net process should know when the export have been finished or if there was any error. The datatype will be all int or nvarchar.

I am assuming that a pure C# code using ado.net to execute a select * command and looping over the datareader and writing to a file for each record will be slow and there is no way I can parallelize this.

Ideally the export will be to a remote shared network folder and not a local folder on the SQL Server machine. The SQL Server will be a HA cluster. Is SSIS better suited for this, no data transformation required?

The .Net process would run on Machine A, SQL Server on Machine B and ultimate file destination is a network share. One option is SQL server writes the file directly to the network share. The other option is SQL Server writes to machine A and then when the file is written the .net process copies it to network share. I don't have formal SLA but expecting 30 mins - 1 hr for the file write.

  • 3
    "Ideally the export will be to a remote shared network folder and not a local folder on the SQL Server machine." -- where will the .NET app run from? Worst-case, this could mean the data has to move through 2 network hops, which will probably be the biggest bottleneck. Also, the number of rows is somewhat irrelevant -- what is the approximate total data size? Do you have a performance SLA you need to meet for this process?
    – Jon Seigel
    Nov 23, 2012 at 17:53

5 Answers 5


Looping through all those articles is an option if you want to get old watching it execute.

Some different options that you should try are:

And you can try all these options while looping in another session, just for fun :-).


I would simply use the Import Export Wizard. At the end you're given the option to save the task, which you can then schedule with SQL Server Agent. Add yourself as an operator and configure DB Mail on the server, and it can email you when the job completes or fails.

Seriously, why reinvent the wheel?


  • Not bad at all, actually this saves an SSIS package at the end. If it's a one time only task it can be a solution.
    – Marian
    Nov 24, 2012 at 11:24

Others have stated that bcp should be the fastest way but I don't see any advantage over a CLR solution. On inserts to database tables, the various bulk copy implementations will always win. This is primarily due to the way that they minimize logging and enable multi-threaded writes. You don't have these constraints when writing to a flat file.

At my work, we use a CLR to dump query output to a file. We also incorporate the Ionic.Zip.dll so that the file can be automatically zipped after creation.

Here's an example which the op claims to be twice as fast as bcp on blobs: https://stackoverflow.com/questions/10325338/fastest-way-to-export-blobs-from-table-into-individual-files

  • That particular question is not very detailed. I'd suggest you read the following question - Optimising BCP performance for BLOB data here. It will surely give an idea about the many playing options you have with BCP only :-).
    – Marian
    Nov 24, 2012 at 11:28
  • I see the links and fully agree. But most of the optimization points are for loading data into sql server. I've yet to see any facts related to bcp being faster than a streamwriter or filestream CLR solution when writing sql data to a flat file.
    – brian
    Nov 24, 2012 at 16:10

You can create a simple SSIS package:

Here's a high-level how to:

  1. Create a OLEDB connection to the database in the connection manager
  2. Drag a Data Flow Transformation into the Control Flow and then click on it to get to the data flow.
  3. Drag a OLEDB Source from the toolbox into the data flow, and edit it so it connects to your desired table using the connection you created in step 1
  4. Drag a Flat File Destination from the toolbox into the data flow and connect the OLEDB source to it.
  5. Select "New" in the Flat File Destination, and it will create a new flat file with the same column structure, and you can use your desired delimiter, or maybe a fixed with file if you like that.
  6. Run it.

Any noob should be able to figure this out. A Anti-GUI person would not like this solution, so save the comments on that regard, it's just an alternative for less-tech savvy individuals who struggle using BCP...

You have a little more flexibility in how the data file is formatted. You can do this in BCP and all that, but this takes the complexity out of it. But there is a small advantage as you can put in a custom file header, and create a "Column Name" as the first row, which makes the flat file more human consumable.

Dont forget to save it, if you created it once, you will likely be asked again! Hope this helps..


I think bcp.exe from commandline should be the fastest way.


  • 5
    While you may be right, could you shed some light on details about this solution? How it is used (a link to the docs may be enough), why do you think it is the faster etc. Nov 23, 2012 at 9:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.