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?

Answer 1

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.