SQL SERVER – How to Set Variable and Use Variable in SQLCMD Mode

Here is the question which I received the other day on SQLAuthority Facebook page. Social media is a wonderful thing and I love the active conversation between blog readers and myself – actually I think social media adds lots of human factor to any conversation. Here is the question –

“I am using sqlcmd in SSMS – I am not sure how to declare variable and pass it, for example I have a database and it has table, how can I make the table variable dynamic and pass different value everytime?”

Fantastic question, and here is its very simple answer. First of all, enable sqlcmd mode in SQL Server Management Studio as described in following image.

SQL SERVER - How to Set Variable and Use Variable in SQLCMD Mode sqlcmd-ex1

Now in query editor type following SQL.

:SETVAR DatabaseName “AdventureWorks2012”
:SETVAR SchemaName “Person”
:SETVAR TableName “EmailAddress
USE $(DatabaseName);
SELECT *
FROM $(SchemaName).$(TableName);

Note that I have set the value of the database, schema and table as a sqlcmd variable and I am executing the query using the same parameters.

SQL SERVER - How to Set Variable and Use Variable in SQLCMD Mode sqlcmd-ex2

Well, that was it, sqlcmd is a very simple language to master and it also aids in doing various tasks easily.

If you have any other sqlcmd tips, please leave a comment and I will publish it with due credit.

Reference: Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – Another lesser known feature of SQL Server Management Studio 2012 – Guest Post by Balmukund Lakhani
Next Post
SQL SERVER – Weekly Series – Memory Lane – #035

Related Posts

19 Comments. Leave new

  • :setvar ServerName “SomeServer”
    :connect $(SomeServer)

    select * from DatabaseName.SchemaName.TableName

    Reply
    • Sorry, edit is below

      :setvar ServerName “SomeServer”
      :connect $(ServerName)

      select * from DatabaseName.SchemaName.TableName

      Reply
      • GRE in Pimpri
        June 28, 2013 2:51 pm

        Can You Tell ME What If i will Forgot to put “.” In Between schema & Table Name??

  • Javier Castillo
    June 28, 2013 8:52 pm

    SQLCMD is very useful when you have to import data from several files into a database. you can set a path into a variable so you can specify each file in the same path…

    :SETVAR path “C:\SQLImportFiles”
    :SETVAR dataBaseName “DBTarget”

    USE $(dataBaseName);
    BULK INSERT person.Person FROM N’$(path)\person.Person.csv’
    WITH (
    CHECK_CONSTRAINTS,
    CODEPAGE=’ACP’,
    DATAFILETYPE=’char’,
    FIELDTERMINATOR=’\t’,
    ROWTERMINATOR=’\n’,
    KEEPIDENTITY,
    TABLOCK
    );
    .
    .
    .

    Reply
  • Hi,
    I really like this post on SQLCMD variables; however, my variable question is slightly different. I hope you can help.
    I am trying to declare my variable value in SQL Server Mgmt Studio and THEN pass that value in to the SQLCMD :Setvar.
    Please see below:
    CREATE TABLE #ServerConnection
    (
    ServerID int primary key
    ,ServerName varchar(50)
    ,DatabaseName varchar(100)
    )

    INSERT INTO #ServerConnection (ServerID, ServerName, DatabaseName) VALUES (1,’ServerName123′, ‘DatabaseNameABC’);

    Declare @ConnectionID int = 1
    Declare @Server varchar(50) = (Select ServerName FROM #ServerConnection WHERE ServerID = @ConnectionID)
    Declare @Database varchar(100) = (Select DatabaseName FROM #ServerConnection WHERE ServerID = @ConnectionID)
    :setvar servername @Server
    :connect $(servername)
    go
    When I run this code i get an error which makes me think it may be impossible to pass a variable value into a SQLCMD variable. Is this true, or can you tell me how to do it?
    Error:
    Connecting to @Server…
    Fatal scripting error. Cannot open connection specified in the SQLCMD script.
    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)

    Reply
    • @melanie…..did you find a solution for your above query. mine is a similar problem, so even im struggling to find a solution

      Reply
    • Exactly what I was attempting to do, set a SQL variable and then pass the value to the command line but could not get it to work.

      Reply
      • did you got any solution for this

      • What is the problem?

      • In this case we try creating a variable in T-SQL by declare @var… set @var = … then we need to use that var in SQLCMD code on the same stored procedure like !!MKDIR “C:”+@var or something that will pass the variable to sqlcmd. We need that something

    • You can set scripting variables to dynamic variables and functions, but then the scripting variables have the same data type as the dynamic variables, and for all intents and purposes become the same, I.e., you can only use them as literal strings or numbers.

      Reply
      • Is this issue resolves?? Is it possible to assign value to a SQLCMD variable form SQLvariable dynamically??
        DECLARE @DBNAme AS VARCHAR(100)
        SELECT @DBNAme = DBNAme FROM #table
        :setvar MYDATABASE @DBNAme
        !!ROBOCOPY $(BACKUPPATH) $(RESTOREPATH) $(MYDATABASE).*

        It is not working,
        @Pinal: Can you please provide any work around or solution to this?

      • Did anyone got this working ?

  • Here is what I did, using single quote with double quote inside it.
    SET @FileName = ‘”c:\test\Test\my1.txt”‘
    SELECT @FileName
    :setvar DatabaseName db_name()

    Reply
    • Just for a complete round trip, the only way I found was to dynamically create a text file and call that such as described here:

      Reply
  • Hi,

    i m using single DB with Multiple Schema..
    Now problem is that i don’t want to create dynamic query in stored procedure for table..
    i want that i send the parameter of schema and it’s set with table and it’s send the result..

    that’s mean, i want to write compiled query in procedure..

    Thanks

    Reply
  • Wallace Houston
    November 22, 2014 2:51 am

    I’m trying to run this from a stored procedure. I’ve done similar things with xp_cmdshell using SQL variables, but this one doesn’t work. It does not throw an error and YES, the table is populated. Thanx!
    declare @vardate varchar(8), @filename varchar(25), @Now datetime
    SELECT @Now = GETDATE()

    select @vardate = (Select CONVERT(varchar, DATEPART(mm,@Now)) +
    CONVERT(varchar, DATEPART(dd,@Now)) +
    CONVERT(varchar, DATEPART(yyyy,@Now)))

    select @filename = ‘”C:SFI_’ + @vardate + ‘.csv”‘
    –select @filename
    Execute xp_cmdshell ‘SQLCMD -S VSQL2 -d Mydata -U user -P password -Q “Select * from tConsigneeOrder” -s “,” -o @filename’

    Reply
  • Great article, and really enjoy watching the various tutorials you’ve posted in the past. I have a question about the usage of the SETVAR feature in SQL Server and wanted to see if this were possible?

    So Dave, can you use your example (from above):

    :SETVAR DatabaseName “AdventureWorks2012”
    :SETVAR SchemaName “Person”
    :SETVAR TableName “EmailAddress”
    USE $(DatabaseName);
    SELECT *
    FROM $(SchemaName).$(TableName);

    But make it look at all columns in all the databases for a specific column name?

    DECLARE @DB_NAME VARCHAR(128);
    DECLARE @CT INT = (SELECT MAX(database_id) FROM [sys].[databases])

    WHILE @CT > 1
    BEGIN
    :SETVAR DatabaseName @DB_NAME
    :SETVAR ColumnName “Person”
    USE $(DatabaseName);
    SELECT *
    FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = $(ColumnName)
    SET @CT = @CT-1
    END

    Reply
  • we do have a scenario, where we have to create the Stored procedure in a specific Schema in Transactional Database, That SP should be picking up data from a staging Db different Schema. Can we dynamically replace these 3 parameters before executing the Query?
    Ex : My SQL file will have below text,
    Create Proc dev.ImportCustomerMaster
    begin
    — Here we have all business logics build in & insert finally into the table

    Insert into customerMaster from StagingDB.dev.customerMaster

    end

    While executing this file in SQL using Sqlcmd function can i replace Schema name : Dev, Stagingdatabase Name ; StagingDB with variables defined in Batch file ?

    This is to handle some interfaces which will pick data from different databases & pull data into transactional DB. While we migrate queries from Different instances like Dev, QA, Prod ..etc, we don’t want to manually replace the staging DB name & Schema name.

    Any suggestions over here ?

    Reply

Leave a Reply

Menu