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.