Select from table referenced in a record


Is it possible to select records from a table when all we have is the table name as string from another select?

Table1 contains
id    table_name
21   a_name_of_table

I would then like to select records from a_name_of_table knowing that the table id is 21 (But not knowing the table name).

Thanks for the tips
Who is Participating?
Anthony PerkinsCommented:
>>Is it possible <<
Yes.  Should you do it? Probably not.  In any case:

Declare @SQL varchar(1000)
Select @SQL = 'Select * From ' + table_name + ' WHERE AddConditionHere'
From Table1
Where id = 21

exec (@SQL)
mimilAuthor Commented:
Thanks acperkins,

What would be the reason for not doing it?
Anthony PerkinsCommented:
The short answer is that it requires Dynamic SQL.  This has performance and security problems associated with it.  I am sure you have heard about SQL injection.  The other problem IMHO is that it could very quickly turn into a maintenance nightmare.

Just my 2 cents worth.
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

"Just my 2 cents worth."

Your 2cents always seem to be on the mark :)
mimilAuthor Commented:
Thanks again acperkins,

I have just read a few article on SQL injection and it was well worth the reading. I am not very concerned as the entire sql statment should be generated from database data rather than user input but I could very well be wrong......

If better solution are available, why not. I am building a reconciliation DB which will match n systems data. I would like to have the various reconciliation pairs definition in a table. From this table and a recon_compo table defining which reconciliation pairs are part of a greater recon, I would like to arrange all the data into a consolidated temp table to be exported to excel using DTS. The issue is that the fields reconciled for a recon pair may vary thus pushing for individual tables to hold each recon exceptions (This is were I am not sure how to build a single table to hold generic data ......).

The initial question was asked to know if I could dynamically from the data in the reconciliation pairs definition in a table build the statement to query a specific recon exception table (Which definition will change depending on the type of recon done).

Hopefully it is clear. Whatever the outcome of this post, acperkins already got the points.
Anthony PerkinsCommented:
Don't get me wrong, I understand what you are trying to achieve.  I have been down that road already, it is just fraught with problems (aside from the dynamic SQL side of it).  If you think you can keep the names of the tables in sync, more power to you.  And trust me I am not being facetious.  I could not do it.
Anthony PerkinsCommented:
P.S. Before awarding any points, make sure you test the code.  It was written off the cuff and totally untested.
mimilAuthor Commented:
Let's try it out and see how it goes ... If it becomes a mess, we'll flatten all the structure and code the logic in SPs ....

Thanks for the tips and the friendly tone.
BTW: The code works as it is.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.