How to Export Large SQL Server Query Results to .txt File? (5 Different Ways)

How to Export Large SQL Server Query Results to .txt File? (5 Different Ways)

If you want to export a large SQL server query to a txt file, there are more than one ways. Which one to choose depends on your preference and credentials available to you. In this article, we explain five ways you can achieve this.

Five Ways to Export SQL Server Query Results to txt file

Here are the five different methods. Review them and proceed with the one that best suits your skills and preference:
  • Using the SQL Server Command Line
The first method is by utilizing the command line provided by SQL server. It also happens to be the easiest method and requires you to execute one code, which is:

Code:
sqlcmd -i c:\sql\sqlquery.sql -o c:\sql\sqlqueryoutput.txt
This command will store the sqlquery.sql file as sqlqueryoutput.txt file, thus achieving the intended goal.
  • Using PowerShell
Instead of the command line, you can execute the code in Powershell to get the desired result. So go ahead and open PowerShell and execute the following command:

Code:
Invoke-Sqlcmd -InputFile "C:\sql\sqlquery.sql" | Out-File -filePath "C:\sql\sqlqueryoutput.txt"
Here, the Invoke-Sqlcmd will the sqlquery.sql and store the output in sqlqueryoutput.txt file.
  • Using the Import/Export Wizard
Wizard is another option to export large SQL Server Query results to a txt file. Here are the steps you need to follow:
  • Right-click on the database inside SSMS on which you want to work
  • From the list, select Import or Export data
  • Go to Export Data under Tasks
  • Open SQL Server Import and Export wizard
  • Select Microsoft OLE DB Provider as the Data Source (this may require you to specify both the connection info and server name)
  • Then from the Destination section, click on Flat File Destination
  • Input the file name and path (where you want to save the file) at the required fields and then click on Next
  • Select Write a query to specify the data to transfer (in this section you’ll have to mention the SQL Server Query and then select Parse in order to verify the query)
  • Lastly, tap on Run Immediately to start the process of exporting the result to a text file
You’ll get a success message when the process is over.
  • Using BCP
BCP or Bulk Copy Program is the fourth way to export large SQL Server Query to .txt file. Since BCP is responsible for importing/exporting data from SQL server, it’s extremely reliable to carry out this operation. And it’s easy to use too.

Open the BCP tool and then execute the following:

Code:
bcp "SELECT TOP 10[PatientEntityID],[NationalIDNumber],[DepartmentNode], FROM Patient2021CZT2.[HumanResources].[Patient] WITH (NOLOCK)" queryout c:\sql\bcpoutput.txt -c -T
The code will direct BCP to export query results to the bcpoutput.txt file. -T is used to indicate that we’re using a Trusted Connection, while -c implies the operation of data type.

If you have millions of rows in your SQL table, using BCP is the fastest method.
  • Using SQL Server Management Studio
The final way is to use the SQL Server Management Studio. This is an great alternative to the Wizard.

Here are the steps involved:
  • Open the SQL Server Management Studio
  • Navigate into Tools > Option
  • Then navigate into Query Results > SQL Server
  • Here, select Resultsto file (in this step you’d need to define the default location for saving the SQL Server query results)
  • Finally, create a query and execute it. You’ll be asked to provide a name and filetype of the file where the output will be saved.
Now you’ll have txt file containing large SQL server query results.

So those were the five ways for carrying out this operation. Go with the one that you find the easiest. In case a particular process didn’t work, try another or contact the hosting support team.
Author
kumkumsharma
Views
3,394
First release
Last update
Rating
0.00 star(s) 0 ratings

More resources from kumkumsharma

Top