Export SQL query results to text file

[Chris McFarling]

What's the most efficient method to export the result set of a SQL query to
a text file using ASP.NET? There could potentially be a few thousand rows.

 

[George]

To export anything will do...
Just open a DataReader and start wrting into file..

To import the best is to use bcp utility or BULK INSERT statement. Since
this does not grow the Transaction Log.

George.

 

[Chris McFarling]

So is row by row the only way to get data out of MSSQL (I'm running v2000
btw) and into a text file? Is there an equivalent to SqlBulkCopy for getting
data out of the database as opposed to into it?

 

[George]

Yes, row by row...
There is no BulkExport only BulkImport. at least I am not aware of).

The "Insert record" operation is pretty expensive. SQL server needs to do a
lot of work.. Hence to optimize it there is an BulkImport operation.
You might run 100000 single Insert operations for hours while BulkInsert
will do it in a minutes.

SELECT is not expensive at all. Hence no need for optimization.

Although you might want to play with transaction level to to avoid
unnecessary locking.
You might want to set transaction level to "READ UNCOMMITTED".
issue "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" right before SELECT
statement.


George.