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

Solution 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.

Solution 2

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

Comments

[Craig]

It's a third-party system that I am interrogating that has 1-N tables loaded into it, with a name-based reference in another table. So what you are saying is that I'd have to first query for unique tablenames then used horror a cursor?

[Martin Smith]

@Craig - See my edited suggestion about a view. I haven't tested this idea though.