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