What is the fastest way to export a table to a text file

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.

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:

• the bcp utility (mentioned above, too, it's a command line tool to import and export data to/from an SQL Server);

• BULK INSERT statement - the T-SQL language sugar to work with data imports from scripts;

• SqlBulkCopy class from .NET - if you want to work from inside the app. A nice example can be seen here: Linchi Shea's Performance Impact: the Most Optimal Insert Script can't Beat BulkCopy .

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

StackExchange Database Administrators Q#29155, answer score: 10