How to output Query results to text file using bcp and queryout

29 August,2014 by Jack Vamvas

Question: How can I output query results to a text file? I’ve tried    Select * from myTable into 'c:\my.txt' and  it errors.

Answer:   You can use bcp and the queryout option.   The queryout option copies from a query and is required when bulk copying data from a query.

An example of bcp and queryout

 

EXEC master..xp_cmdshell'bcp "SELECT  *  FROM MyTable" queryout "c:\mytext.txt" -c -T -x'

 

If xp_cmdshell is unavailable , a quick and dirty fix is the SSMS query output option to a text file

 

Read More on exporting data

SQL Server – Powershell Excel to HTML - SQL Server DBA

Export Excel data to SQL Server with Powershell - SQL Server DBA

Powershell - run script on all sql servers - SQL Server DBA

Export-CSV Powershell - SQL Server DBA

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to output Query results to text file using bcp and queryout


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer