I utilized SQLCMD so that we could parameterize the databases and not use dynamic SQL. If you're not familiar with SQLCMD mode, please read here. In SSMS, you can turn on SQLCMD mode by going to Query > SQLCMD Mode
In my query I used the following variables like such:
SET STATISTICS IO OFF;and in the script:
SET STATISTICS TIME OFF;
:setvar mainDatabase "maindb"
:setvar incomingDatabase "incdb"
...
FROM
[$(incomingDatabase)].dbo.VENDORS m
LEFT JOIN
[$(mainDatabase)].dbo.VENDORS i ON
m.vendorid = i.vendorid
WHERE i.vendorid IS NULL;
All is well and good since the script merges multiple databases, I just change the name of the incomingDatabase and run it again. The problem is, I have to babysit the script, change the variable's value and rerun when I could be doing more important things, like blogging! You can also run SQLCMD from the command line, which has brought me to...
Enter FOR /F in batch files:
@echo off
setlocal EnableDelayedExpansion
set Server=sql2008r2
set MainDatabase=MainDatabaseName
set UserName=sa
set Password=Password!@#$%^&*
set Databases=DatabaseOne^
DatabaseTwo^
DatabaseThree^
DatabaseFour
echo Restoring from backup
sqlcmd -i C:\WorkingDirectory\RestoreDatabasesFromBackup.sql -S %Server% -U %UserName% -P %Password% -o C:\WorkingDirectory\RestoreDatabasesFromBackup.log
...
echo Vendors
FOR /F "tokens=*" %%G IN ("!Databases!") DO (sqlcmd -v mainDatabase="%MainDatabase%" incomingDatabase="%%G" -i C:\WorkingDirectory\2-VENDORS.sql -S %Server% -U %UserName% -P %Password% -o C:\WorkingDirectory\2-VENDORS-%%G.log)
This batch file allows you to run the script for each of the databases in the multiline variable Databases. The caret (^) and following blank line are required. It looks a little sloppy, but it is what it is. Please let me know if you have a better way to do this by describing it in the comments below. Please don't say, "PowerShell."