is there a way to dynamically join between tables?

Given tabes A and A1 - A100 with these schemas:

CREATE Table A(
ID INT NOT NULL,
Value1 VARCHAR(10) NOT NULL,
TableName VARCHAR(4) NOT NULL
)

INSERT INTO A
(1, 'Val1', 'A1'),
(2, 'Val2', 'A5')

CREATE TABLE A1( --and same for tables A2 - A100
ID INT N开发者_StackOverflowOT NULL,
Value2 VARCHAR(10) NOT NULL
)

INSERT INTO A1
(1, 'Val74')
INSERT INTO A5
(1, 'Val39')

How can I do the following? (pseudo-code)

SELECT A.Value1, X.Value2
FROM A INNER JOIN X ON A.TableName = X

And produce:

Value1  Value2
Val1    Val74
Val2    Val39

You would need dynamic SQL to dynamically join between tables.

If you have 100 different tables with the same schema are you sure they shouldn't all be consolidated into one table with a "type" field though?

In any event you could simulate this with a view

CREATE VIEW AView
AS
SELECT 'A1' AS name , ID, Value2
FROM  A1
UNION ALL
SELECT 'A2' AS name , ID, Value2
FROM  A2
UNION ALL ...

You would need to check the execution plan and output of SET STATISTICS IO ON to be sure that your queries weren't touching unnecessary tables. You might need the RECOMPILE hint.

This is a perfect example of how you can build your query using dynamic sql. It will give you the best possible performance with your current setup, and it is short and easy to read.

DECLARE @sql varchar(max)
SELECT @sql = coalesce(@sql + ' UNION ALL ', '')
                  +'SELECT A.Value1, '+tablename
                  +'.Value2 FROM A INNER JOIN '
                  + tablename + ' ON A.TableName = '''
                  +tablename +''''
FROM A

Result:

Value1     Value2
---------- ----------
Val1       Val74
Val2       Val39

You can't use data as a table name in a query. Besides, what you are trying to do would not be a join, it would be a subquery, as each record in table A could use a different table.

To do something like that you would have to build a query dynamically, and you need a separate query for each record in the A table, or at least a separate query for each distinct TableName value.