Different ways to export SQL Server data to text file

This blog explains few methods you can use to export data from SQL Server table to a text file.

Consider the below table as an illustration

The requirement is to export data to a text file.
Now lets see different ways by which we can achieve this.

1. Using bcp utility
    bcp is a command line utility which can be used for exporting sql server data to text file. The syntax will look like below

EXEC xp_cmdshell 'bcp "SELECT * FROM DBName.dbo.Manufacturers" queryout "F:\manufacturer.txt" -S "ServerName" -T -c'

The output file will be as follows

The above command uses trusted connection -T and will only work if you use windows authentication for connecting to the server.
If you want to use SQL Server authentication instead you need to pass username and password as

EXEC xp_cmdshell 'bcp "SELECT * FROM DBName.dbo.Manufacturers" queryout "F:\manufacturer_sqllogin.txt" -S "ServerName" -U UserName  -P  Password -c'

The main caveat in using this method is that you need to use xp_cmdshell extended procedure for invoking bcp from t-sql and hence this method cant be used if  xp_cmdshell is not enabled by the database administrator.
Also it doesnt include column headers by default in the output file and you've to include separate query to return column headers if you need them in the file.

2. Using OPENROWSET function
The query will look like below

INSERT OPENROWSET  ('Microsoft.Jet.OLEDB.4.0', 'Text;Database=;','SELECT * FROM .txt')
SELECT * FROM DBName.dbo.Manufacturers

The file format needs to be set in registry under
HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ Jet \ 4.0 \ Engines \ Text .

You may use values like Format = TabDelimited/Delimited/CSVDelimited etc

3. Using osql utility
osql is a command line utility available in SQL Server. This can be used for executing a Transact SQL script and exporting the results to a text file. The command can be executed from command line or you may use extended procedure xp_cmdshell to execute it from SQL Management Studio (SSMS) window.
The syntax will look like below

EXEC xp_cmdshell 'osql -Q "SELECT * FROM DBName.dbo.Manufacturers" -o "F:\queryoutput.txt" -S "ServerName" -E'

if you're calling it from SSMS using xp_cmshell.
As before -E stands for trusted connection so if you want to use SQL Authentication instead you need to replace it with the below

EXEC xp_cmdshell 'osql -Q "SELECT * FROM DBName.dbo.Manufacturers" -o "F:\queryoutput.txt" -S "ServerName" -U UserName -P Password'
The same caveat is applicable here too as in the case of bcp as this also requires xp_cmdshell for its invocation.