I receive lots of email everyday and I do answer almost every email I receive. However, I prefer to answer all the technical questions on my facebook page. With the help of social media it is very easy to reach out to multiple people with the same problem. Here is the recent question which I received on my facebook page.
“How I automatically execute my T-SQL script and save output of the query (resultset) in a different file automatically? I do not want to use SSMS”
Well, very interesting and simple question. It is very easy to direct the output of the query to a new file with the help of sqlcmd.
Let us work on this together.
Step 1: Create SQL file which you want to execute. I have created SQL file testquery.sql and saved it in my drive.
Step 2: Execute following sqlcmd command on prompt:
sqlcmd -i SQLFile.sql -S ServerLocation -E -o OutFileName.txtIf you are using windows authentication execute following script
sqlcmd -i testquery.sql -S localhost -E -o TestResults.txt
If you are using username and password run following script
sqlcmd -i testquery.sql -S localhost -U pinaldave -P password -o TestResults.txt
Step 3: Open the file generated by sqlcmd in the drive
This is indeed a very simple process. If you have any question, you can go to my Facebook Page and ask question.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
22 Comments. Leave new
You can rename the file in a seperate step to get a unique name by adding a date in a roundabout way using a windows variable, eg:
REM SQLCMD set filename variable name
REM -U user, P password, Q quit after query, nocount on, -o output file -h-1 remove column headers, -W remove trailing spaces
sqlcmd -S server -U user -P pass -Q”SET NOCOUNT ON; SELECT REPLACE( CONVERT(VARCHAR,MAX(StartDate),103) + ‘_’ + CONVERT(VARCHAR,MAX(EndDate),103),’/’,” ) FROM database.dbo.DateRecord” -oDateVar.txt -h-1 -W
REM create environment variable PGvar
set /p DateVar= < DateVar.txt
REM rename files based on variable
rename OriginalOutput.txt OriginalOutput_%DateVar%.txt
It answered my question !!!
That answered my question !
what does -E and -S stand in the above command???
I have executed many query but in my output file I only get one line, whereas when I run the sql scripts manually from query analyser it shows (1 rows affected) multiple times down the results window, how can i get these multiple rows of output to show in my output text file using SQLCMD..?
how can export the table data.
can we do this using t sql?
Using utilities we have lot of options like SQLCMD, BCP, OSQL, but how can we do this using TSQL?
SQLCMD is just a tool which would run T-SQL behind the scene.
how do we get rid of the extra blank lines between each row in the result set?
Which Blanks? Are they part of the data which has been fetched?
Good day
I tried the above but my output file is not created. Please help
when i execute the sql query the results are displayed on the results pane but when i run the command to automatically save the results in text file, output file is created successfully, when i open it it shows there are some errors in the script though i ran the script and it displayed the results. Why?
Works great. But is there a way to only output the results without the dashes and
(1 rows affected)?
How i remove the hyphen (—————–) which are in the second line of the CSV file? Many thanks
my first row is blank, and I have 4 rows of dashes. I would like to know if I can eliminate these, as well as the “(X row(s) affected)” at the bottom
What if I want to store my query result into text file using SQL management studio script
Without that CLT+SHIFT+F . With script like in Mysql INTO OUTFILE
Where do you save the sql file to be run, C drive, D drive etc.?
If you just give file name then it should be in the directory in which you are. Else you can also give complete path like sqlcmd -i”D:\ThisFolder\Filename.txt”
How to save output in excel using cmd
How can I add the date to the output file? ex: C:\Temp\OutFile20210615.txt
I have a table with 1,55000 records ,File is generating but only for 19,000 records. it is NOT contains all the 1,55000 records ,
I am using sqlcmd to generate output file.
could any one help on this?