I am writing a batch script that uses SQLCMD
to dump tables from a database server onto the local machine. For that I construct the query such that I have the flexibility to specify just the table name. The query gets constructed depending on the table name, which then gets used in the SQLCMD
command. The code snippet to construct the query is shown below:
@echo off
SET tableName = testDB
SET dumpTable="SET NOCOUNT ON; SELECT * FROM %tableName%"
ECHO %dumpTable%
On running this script I am getting the following output
"SET NOCOUNT ON; SELECT * FROM "
The tableName
variable is not getting substituted in the set statement. How should I modify my script to achieve this output:
"SET NOCOUNT ON; SELECT * FROM testDB"
You must not have whitespace around the =
sign in variable assignments. Replace this:
SET tableName = testDB
with this:
SET tableName=testDB
Also, you shouldn't have double quotes around the value in a variable assignment, because that way the double quotes will become part of the value. It's better practice to put double quotes around the whole assignment, and then quote the variable as necessary when it's used, e.g.:
set "tableName=testDB"
set "dumpTable=SET NOCOUNT ON; SELECT * FROM %tableName%"
sqlcmd -d database -q "%dumpTable%"