SQL join from multiple tables

Question

Hi all

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 output.UID, output.description, input.data from output.dbo.description
LEFT JOIN
(SELECT input.UID, input.data FROM [output.sourcedb].dbo.datatable ) AS input
ON input.UID=output.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?

Answer 1

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') 

Answer 2

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.