Export SQL Result To A Text File

Introduction

This article gives an overview of how to export SQL query result to a text file. Sometimes, we need to keep a backup of an existing table data record. That time, this requirement comes in to picture. In my project, there is a requirement when I need to create an SQL script that updates the existing table records but before updating a record, we need to keep the existing table record as a backup.

We have the below 8 ways to do this.

1. Show results to a file in SQL Server Management Studio (SSMS)
2. SQLCMD – To run the script on command prompt (cmd).
3. PowerShell
4. Import/Export Wizard in SSMS
5. SSIS Wizard (almost the same than the number 4, but we are using SSDT instead of SSMS to create the package).
6. C#
7. SSRS
8. BCP

By using the above options, we can export the results in our report. I have described #2 to export an SQL script report into a text file.

Let’s have a look at the steps and example of how to export SQL script report using SQLCMD.

SQLCMD is the SQL Server Command Line utility. You can save the results in a file from here. This option is useful when you are using batch files to automate the tasks.

1. Create a new table and insert the data into this table. I have created table ‘Employee’; the record is inserted.
   
   
   

2. Now, create an SQL script that updates ‘Employee’ table. So, I have created the following script. To take backup of the existing record, I have added a SELECT query for backup.
   
   1. SELECT [EMPLOYEE_ID], [FIRST_NAME], [LAST_NAME], [SALARY], [JOINING_DATE], [DEPARTMENT]
   2. FROM [TestExam].[dbo].[Employee]
   3. UPDATE [TestExam].[dbo].[Employee]
   4. SET SALARY=33333
   5. WHERE [EMPLOYEE_ID]=2

3. Save this script on a local path. We need this path into sqlcmd.
   
   
   
   Path - C:\ myScript.sql

4. Now, open the command prompt in administrator mode.
   
   
   

5. Paste the following command in command prompt [Verify all below details before pressing Enter]. This command will execute the SQL script.
   
   sqlcmd -S SAGAR-PC\SQLEXPRESS -d databaseName -U sa -P pass@123 -i c:\myScript.sql -o c:\myoutput.txt
   
   
   

Parameter Details

Sqlcmd Parameters	Value	Description
-S	SAGAR-PC\SQLEXPRESS	Data Source
-d	Db_Name	Data base name
-U	sa	User name
-P	pass@123	Password
-i	c:\myScript.sql	Input file path
-o	c:\myoutput.txtORc:\myoutput.csv	Output file path

Note

If you don’t have –S, -d, -U, -P parameters, then you can remove this. This is an optional parameter.