I want the output of an SQL query Select count(*) from Table to be used as a variable in a Batch script. I am using sqlcmd to execute this query, but haven't figured out how to do this properly.
Any help or examples would be appreciated.
Add a comment
|
4 Answers
Here is solution I used to get output from query in a batch script variable. This is used in a windows .cmd batch script file. Basically it outputs to a temp file and then extracts the text into a variable.
sqlcmd.exe <SERVER & DB OPTIONS> -h-1 -Q "SET NOCOUNT ON; SELECT COUNT(*) FROM Table" -o output.txt
set /P recCount= < output.txt
echo %recCount%
del output.txt
Notice the use of the -h-1 flag (which tells SQLCMD to suppress column headings in the output) and NOCOUNT within in the SQL command (to suppress the count of rows affected). Also if the query was being made for a string you may want to use the -W parameter to remove trailing spaces from the output.
Comments
Assuming that you're talking about Microsoft's SQLCMD on Windows (and your Bash is therefore a cygwin or msys or such-like)
VAR=`sqlcmd.exe <SERVER & DB OPTIONS> -r1 -h-1 -Q "SET NOCOUNT ON; SELECT COUNT(*) FROM Table"`
should do it nicely for you.
Comments
You can do this in Bash Script
SQLCMD="sqlcmd -h -1 -S $SERVERNAME -U $USERNAME -P $PASSWORD -d $DBNAME -Q"
var=$($SQLCMD " SET NOCOUNT ON; SELECT COUNT(*) FROM TableName ")
echo "Value: $var"
Comments
Try this:
DECLARE @abr int
SELECT @abr = COUNT(*) FROM mytable
:setvar abr (@abr)
print $(abr)
