Setting database name as a variable in SQL

Question

currently, I'm doing a bit of a data migration between one db to another based on some business rules.

I have this huge script that I'm writing where I'm referencing both databases a lot of times. The problem is that this data migration is currently in development, at some point I am going to want to have to do it in production with two different databases.

Instead of referencing the database name directly like so

Insert Into Database2.dbo.Table1    
Select * from Database1.dbo.Table1

I would like to somehow just reference the database at the start of the script. So that I can just change the one variable when I change databases.

Is that possible?

Only means I know of relies on dynamic SQL. Otherwise, create one script. Copy it, and do a replace on the references for the intended source & target database references. — OMG Ponies
– OMG Ponies, Commented Mar 16, 2011 at 5:16
possible duplicate of How to use variable for database name in t-sql — cdonner
– cdonner, Commented Jun 3, 2012 at 4:26

Remus Rusanu · Accepted Answer · 2011-03-16 05:23:44Z

9

Use SQLCMD variables:

:setvar dbfrom database1
:setvar dbto database2

Insert into [$(dbto)].dbo.Table1
select * from [$(dbfrom)].dbo.Table2;

This syntax works in sqlcmd.exe (from scripts), as well as in Management Studio, see Editing SQLCMD Scripts with Query Editor. Scripting also allows you to set the variables from the command line:

sqlcmd /E /S server /i script.sql /v dbfrom=database1 /v dbto=database2

I also have a library that allows you to use sqlcmd variables and scripts from applications available as source on Google Code.

answered Mar 16, 2011 at 5:23

Remus Rusanu

296k42 gold badges459 silver badges583 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Diskdrive Over a year ago

thanks that's good. although the intellisense stops working but i don't think there is a solution that caters for that.

Collectives™ on Stack Overflow

Setting database name as a variable in SQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related