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

Question

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.

Answer 1

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 :-).

Answer 2

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?

http://msdn.microsoft.com/en-us/library/ms140052.aspx

Answer 3

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

Answer 4

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..

Answer 5

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

http://msdn.microsoft.com/en-us/library/ms162802.aspx