CODEWITHSUNDEEP
t-sql
KenD
KenD

Reputation: 5318

SQL join from multiple tables

We've got a system (MS SQL 2008 R2-based) that has a number of "input" database and a one "output" database. I'd like to write a query that will read from the output DB, and JOIN it to data in one of the source DB. However, the source table may be one or more individual tables :( The name of the source DB is included in the output DB; ideally, I'd like to do something like the following (pseudo-SQL ahoy)

select o.[UID]
      ,o.[description]
      ,i.[data]
from [output].dbo.[description] as o
    left join (select [UID]
                    ,[data]
                from
                    [output.sourcedb].dbo.datatable
                ) as i
        on i.[UID] = o.[UID];

Is there any way to do something like the above - "dynamically" specify the database and table to be joined on for each row in the query?

Upvotes: 3

Views: 235

Answers (2)

krock
krock

Reputation: 29619

Try using the exec function, then specify the select as a string, adding variables for database names and tables where appropriate. Simple example:

DECLARE @dbName VARCHAR(255), @tableName VARCHAR(255), @colName VARCHAR(255)
...
EXEC('SELECT * FROM ' + @dbName + '.dbo.' + @tableName + ' WHERE ' + @colName + ' = 1')

Upvotes: 2

Bill Karwin
Bill Karwin

Reputation: 562230

No, the table must be known at the time you prepare the query. Otherwise how would the query optimizer know what indexes it might be able to use? Or if the table you reference even has an UID column?

You'll have to do this in stages:

  1. Fetch the sourcedb value from your output database in one query.

  2. Build an SQL query string, interpolating the value you fetched in the first query into the FROM clause of the second query.

    Be careful to check that this value contains a legitimate database name. For instance, filter out non-alpha characters or apply a regular expression or look it up in a whitelist. Otherwise you're exposing yourself to a SQL Injection risk.

  3. Execute the new SQL string you built with exec() as @user353852 suggests.

Upvotes: 1

Related Questions