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.

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.

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 (http://blog.sqlauthority.com)

About these ads

10 thoughts on “SQL SERVER – How to Set Variable and Use Variable in SQLCMD Mode

  1. 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
    );
    .
    .
    .

  2. 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)

    • 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.

  3. 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()

  4. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s