Sql Query Select From [value From Column Of Another Table]

I have a table X where a trigger will insert a row when there's a changes to some tables. I've inserted the table name into table X. Now, I would like to select the data from tabl

Solution 1:

Executing

select'SELECT X.a, Y.b, Y.c FROM X
INNER JOIN [' + X.TableName + '] AS Y ON X.ID = Y.ID where x.primarykey =' + x.primarykey from x

Will output a series of sql statements like

SELECT X.a, Y.b, Y.c FROM X
INNER JOIN [ customer ] AS Y ON X.ID = Y.ID
where x.primarykey = 1234

that you can then execute "sql to build sql" if you will.

Solution 2:

No, that is not possible. You can't use values as table names directly in a query, and you can't join each record against a different table.

You would have to make the join for a single record, and create the query dynamically to use a value as table name:

declare@namevarchar(50)
set@name=select TableName from X where ID =42exec('select X.a, Y.b, Y.c from X innner join '+@name+' as Y on Y.DI = X.ID where X.ID = 42')

Solution 3:

With dynamic query:

DECLARE@tableAS NVARCHAR(128);
DECLARE@sql NVARCHAR(4000);

-- of course you'll have to add your WHERE clause here SELECT@table= TableName FROM X;
SET@sql='SELECT X.a, Y.b, Y.c FROM X INNER JOIN '+@table+' AS Y ON Y.ID = X.ID';

EXEC(@sql);