SQLCMD to export data in txt file

info-59812, 2016-10-12 (first published: 2016-09-27)

By using SQLCMD we can export data from SQL Database to a text file in two ways

By using SQLCMD on Command Prompt
By selecting SQLCMD Mode in SSMS

By using Command Prompt is as simple as running the command which is mentioned in code section, but please don't forget to replace server name, instance name and database name as per your environment.

By using SQLCMD Mode in SSMS

First you need to select SQLCMD Mode in SSMS by selecting QUERY Menu

then run the code below

Here you can see first 3 rows are highlighted which shows SQLCMD command, actually " : " tells SSMS that next is SQLCMD command

In first line we are checking if target file already exist then delete it first because in 3rd line of code we are creating target text file.

Second line of code is connecting with SQLServer Instance

Third line is creating Target file

In both ways , one text file will be created on c:drive with column heading and footer detail messages.

/**** From Command Prompt ****/sqlcmd -S servername\instancename -d Source_DBname -o c:\employee.txt -Q "select * from employee" /*** From SQLCMD Mode of SSMS ***/:!!if exist c:\employee.txt del c:\employee.txt :connect localhost\SQL2008R2 :out c:\employee.txt use Baseline_DB_Aug_2016; select * from employee;