Parameterizing a database name in a SQL script

Question

I'm writing a data migration script to import data from 3 separate DBs with identical schema into a new database. All databases reside on the same SQL Server instance.

It seems very wasteful to copy and paste the queries 3 times over:

insert NewTable(NewField1, NewField2)
select OldField1, OldField2 
  from OldDb1..OldTable

insert NewTable(NewField1, NewField2)
select OldField1, OldField2 
  from OldDb2..OldTable

insert NewTable(NewField1, NewField2)
select OldField1, OldField2 
  from OldDb3..OldTable

... and obviously it will be a nightmare to maintain if I need to tweak the script... plus I count myself lucky that there are only 3 databases, but what if there had been 100?

Bottom line, I would want to write a script that takes the database name as a parameter, so that I can just run one script 3 times, e.g.

create proc MigrateData(@db database)
as
insert NewTable(NewField1, NewField2)
select OldField1, OldField2 
  from @db..OldTable

...but of course that syntax won't work.

I could do this:

create proc MigrateData(@db varchar(50))
as
begin
  declare @cmd varchar(max);
  set @cmd = 'insert NewTable(NewField1, NewField2)
    select OldField1, OldField2 
    from '+@db+'..OldTable';

  exec @cmd;
end

But... yuck!

How do I do it?

DavidG · Accepted Answer · 2014-11-13 14:48:49Z

2

Instead of SELECTing from an external database, why not switch it and INSERT into an external database? that way you can run the script from each external database, or just change the first line to use the correct source.

For example:

USE OldDatabase1

GO
insert NewDatabase.NewTable(NewField1, NewField2)
select OldField1, OldField2 
  from OldTable

Or drop the USE statement and let the connection determine the correct database to start with.

Alternatively, you can likely only achieve what you want using dynamic SQL, which is, like you say, yuck.

answered Nov 13, 2014 at 14:48

DavidG

120k13 gold badges232 silver badges239 bronze badges

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

2 Comments

Shaul Behr Over a year ago

Wow. That is truly facepalm-worthy on my part. An obvious and simple answer that I am ashamed I didn't think of myself. Thanks! :-)

DavidG Over a year ago

It's all too easy to miss something like this when you are buried right inside it. :)

Collectives™ on Stack Overflow

Parameterizing a database name in a SQL script

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related