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.
   

   SELECT [EMPLOYEE_ID], [FIRST_NAME], [LAST_NAME], [SALARY], [JOINING_DATE], [DEPARTMENT]
   FROM [TestExam].[dbo].[Employee]
   UPDATE [TestExam].[dbo].[Employee]
   SET SALARY=33333
   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.

If there is any error while executing the command, then it gives us the error. So please, check CMD result and the output file result.



Report



If you want a report in CSV file, you can also generate this. Just change –o file path from .txt to .csv.

Verify Database



I have provided the following total list of sqlcmd parameters.

sqlcmd Parameters
-a	packet_size
-a	(dedicated administrator connection)
-b	(terminate batch job if there is an error)
-c	batch_terminator
-C	(trust the server certificate)
-d	db_name
-e	(echo input)
-E	(use trusted connection)
-f	codepage | i:codepage[,o:codepage] | o:codepage[,i:codepage]
-h	rows_per_header
-H	workstation_name
-i	input_file
-I	(enable quoted identifiers)
-k[1 | 2]	(remove or replace control characters)
-K	application_intent
-l	login_timeout
-L	[c] (list servers, optional clean output)
-m	error_level
-M	multisubnet_failover
-N	(encrypt connection)
-o	output_file
-p[1]	(print statistics, optional colon format)
-P	password
-q	cmdline query
-Q	cmdline query (and exit)
-r[0 | 1]	(msgs to stderr)
-R	(use client regional settings)
-s	col_separator
-S	[protocol:]server[\instance_name][,port]
-t	query_timeout
-u	(unicode output file)
-U	login_id
-v	var = "value"
-V	error_severity_level
-w	column_width
-W	(remove trailing spaces)
-x	(disable variable substitution)
-X[1]	(disable commands, startup script, environment variables and optional exit)
-y	variable_length_type_display_width
-Y	fixed_length_type_display_width
-z	new_password
-Z	new_password (and exit)
-?	(usage)

Conclusion

I hope you will get an idea of how to generate text or csv file report while executing the SQL script.

I hope you enjoyed the tips while playing with SQL Server. I would like to have feedback from my article readers. Your valuable feedback, questions, or comments about this article are always welcome.