is there a way to dynamically join between tables?

Question

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 NOT 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

Answer 1

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.