Export large SQL Server query result to .txt file like a pro with Bobcares by your side.

At Bobcares, we offer solutions for every query, big and small, as a part of our SQL Server Support Services.

Let’s take a look at how our SQL Server Support Team is ready to help customers with exporting large SQL Server query result to .txt file.

How to export large SQL Server query result to .txt file

If you are looking for a suitable way to export your SQL server query results to a .txt file, you have come to the right place. Our Support Team has put together 5 different methods to get the job done. Let’s take a look at each one to find one that suits our requirements.

Top 5 ways to export large SQL Server query results to a text file

• SQL Server Command Line
• PowerShell
• Import/Export Wizard
• BCP
• SQL Server Management Studio

Via SQL Server Command Line

The SQLCMD or the SQL Server Command line is a useful utility that serves our purpose in this scenario. Moreover, it is extremely handy when it comes to using batch files while automating tasks.

sqlcmd -i c:\sql\sqlquery.sql -o c:\sql\sqlqueryoutput.txt

This command stores the result of the earlier sqlquery.sql file to the sqlqueryoutput.txt file. This is an easy method and exports results of large SQL Server queries with ease to a text file.

Via PowerShell

Alternatively, we can use PowerShell to export our SQL Server query results to a text file with the following command:

Invoke-Sqlcmd -InputFile "C:\sql\sqlquery.sql" | Out-File -filePath "C:\sql\sqlqueryoutput.txt"

Invoke-Sqlcmd calls sqlquery.sql and stores the output in the sqlqueryoutput.txt file.

Via Import/Export Wizard

However, if you prefer to export SQL query results to a text file via a Wizard, we have your back.

1. To begin with, right-click the database in SQL Server Management Studio or SSMS.
2. Then, select the Import or Export data option and head to Export Data under Tasks.
3. Next, open the SQL Server Import and Export wizard.

   
4. After that, choose Microsoft OLE DB Provider as the Data Source. We may have to specify the server name as well as connection information.
5. Then, choose Flat File Destination in the Destination section. Then mention the file name and path where we want to save the text file and click Next.
6. Next, choose the Write a query to specify the data to transfer. Here we will mention the SQL Server query and click Parse to verify the query. Our experts suggest sticking to the default values of the Row delimiter and Column delimiter.
7. Finally, click Run Immediately in order to export the result to a text file immediately.

Via BCP

Another popular way to export SQL Server query results to a .txt file is BCP. Bulk Copy Program or BCP comes with the SQL Server. In fact, it is responsible for importing and exporting data from the SQL Server. Moreover, it is fast, making it a great option if we have millions of rows.

For instance,

bcp "SELECT TOP 10[PatientEntityID],[NationalIDNumber],[DepartmentNode], FROM Patient2021CZT2.[HumanResources].[Patient] WITH (NOLOCK)" queryout c:\sql\bcpoutput.txt -c -T

In the above example, we are exporting the query results to a text file, bcpoutput.txt. -T indicates we are using a Trusted Connection and -c indicates an operation of data type.

Via SQL Server Management Studio

Although we can use a wizard to get the job done via SSMS, we can also perform the same task without relying on a wizard.

1. 1. First, head toTools > Option.
   2. Next, navigate to Query Results> SQL Server and choose the option Result to file. At this point, we can also specify the default location for saving query results.
   3. Then, create a query and execute it. Now, we will be able to specify the name and filetype of the file where we want the output to be saved to.

According to our SQL Support Team, these five methods will work like magic while exporting the results of a large SQL Query to a text file. Let us know in the comments which method worked for you.

[Need assistance with a different issue? We are available 24/7.]

Conclusion

In a nutshell, our skilled SQL Server Support Engineers at Bobcares took us through exporting large SQL Server query result to .txt file.