Dynamic Database Name and Schema/Owner assignment.

Question

I need to update a table from another table several times in succession. In order to make this script easier for people to use, I'd like to be able to dynamically reference it. Something like this:

declare @databasename sysname
set @databasename = 'm2mdata01.dbo'

select * from @databasename.mytable

This isn't working. Any suggestions as to how I can accomplish this?

Answer 1

You can't use variables in the FROM clause in a SQL statement. You would have to use dynamic SQL, such as this:

declare @databasename sysname
set @databasename = 'm2mdata01.dbo'
EXEC ('select * from ' + @databasename + '.mytable')