SQLCMD -- Part VII (Concatenating string with a Scripting Variable)

This example demonstrates the way to create a variable and make use of it for multiple purpose.

Actually in this example we would see how to create a variable and append strings into it. Lets create two Database with slight difference in the name. For example, DB1 and DB2. Then create a table in each DB and populate few records into it.

---Code snippet which needs to be run in Mgmt Studio starts here---
Use master
go

Create Database DB1
go

Use DB1
go

Create table t1 (a int)
go

insert into t1 values (1)
insert into t1 values (2)
insert into t1 values (3)
go

Create database DB2
go

Use DB2
go

Create table t2 (Num int)
go

insert into t2 values (4)
insert into t2 values (5)
insert into t2 values (6)
go

--Code snippet to be run in Mgmt studio ends here---

Now open command prompt and connect into the DB.

Step 1: SQLCMD -U sa -P hot -S VADIVEL
Step 2: Press Enter

Now lets create a variable by name "dbname" and assign 'DB' as the string to it.

Step 3: :setvar dbname DB

Now lets make use of this variable and list out all records from table t1

Step 4: use $(dbname)1
Step 5: go
Step 6: select * from t1
Step 7: go

If you see along with the variable I have appended the value 1. So that it would take it as 'DB1'. Hope I have made the point!

Now lets make use of this variable and list out all records from table t2 which exists within DB2 database

Step 8: use $(dbname)2
Step 9: go
Step 10: Select * from t2
Step 11: go