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.

    SQL Server

  1. 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  

  1. Save this script on a local path. We need this path into sqlcmd.

    SQL Server

    Path - C:\ myScript.sql

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

    SQL Server

  1. 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

    SQL Server

Parameter Details

Sqlcmd ParametersValueDescription
-SSAGAR-PC\SQLEXPRESSData Source
-dDb_NameData base name
-UsaUser name
-Ppass@123Password
-ic:\myScript.sqlInput file path
-oc:\myoutput.txtORc:\myoutput.csvOutput 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.

SQL Server

Report

SQL Server

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

Verify Database

SQL Server

I have provided the following total list of sqlcmd parameters.

sqlcmd Parameters
-apacket_size
-a(dedicated administrator connection)
-b(terminate batch job if there is an error)
-cbatch_terminator
-C(trust the server certificate)
-ddb_name
-e(echo input)
-E(use trusted connection)
-fcodepage | i:codepage[,o:codepage] | o:codepage[,i:codepage]
-hrows_per_header
-Hworkstation_name
-iinput_file
-I(enable quoted identifiers)
-k[1 | 2](remove or replace control characters)
-Kapplication_intent
-llogin_timeout
-L[c] (list servers, optional clean output)
-merror_level
-Mmultisubnet_failover
-N(encrypt connection)
-ooutput_file
-p[1](print statistics, optional colon format)
-Ppassword
-qcmdline query
-Qcmdline query (and exit)
-r[0 | 1](msgs to stderr)
-R(use client regional settings)
-scol_separator
-S[protocol:]server[\instance_name][,port]
-tquery_timeout
-u(unicode output file)
-Ulogin_id
-vvar = "value"
-Verror_severity_level
-wcolumn_width
-W(remove trailing spaces)
-x(disable variable substitution)
-X[1](disable commands, startup script, environment variables and optional exit)
-yvariable_length_type_display_width
-Yfixed_length_type_display_width
-znew_password
-Znew_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.

X

Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now