Select from tables dynamically (table is variable)

Question

1.00/5 (1 vote)

See more:

I have the case where I need to perform a query over a table that i do not know its name at design time.

For example, I have the below tables:

Table_Items_1
Table_Items_2
Table_Items_3
Table_Items_4
Table_Items_5

I need to select depending on a number "x" which refers to the suffix of the table. (Number of tables is dynamic, I cannot do if-else).

This can be done using dynamic SQL. However, I need to have it more generic, since it will be referenced by several other functions/SPs.

I was thinking of Table Valued Functions, since I need to perform other queries over the above query (i.e.: inner joins, other select queries that depend on the result or fields etc.) but Table valued functions do not accept calling stored procedures nor dynamic SQL.

P.S.: I do not mind changing the method, TVF are not a must, yet, I need something generic that can be references from other stored procedures/functions.

Any solution?

Thank you.

What I have tried:

dynamic sql, table valued functions, stored procedure being called from tvf, tvf that contains dynamic sql

Posted 9-Feb-16 23:03pm

tareksnow

Updated 9-Feb-16 23:04pm

v2

Add a Solution

Comments

Richard Deeming 10-Feb-16 8:49am

How dynamic are the tables? If you created a view using UNION ALL to select the records from all five tables, with the table number included as a column in the SELECT list, you'd only need to modify the view when you added a new table (or dropped an existing one).

But this sounds like an extremely poor design.

tareksnow 10-Feb-16 9:42am

Our table are gigantic, database size over 1TB. This is why we are trying to split the data in different tables, hence we cannot use the UNION.
We already have all the data in 1 table, but we need to split it.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

E.F. Nijboer · Answer 1 · 2016-02-10T00:32:00

Solution 1

Why dynamic sql? It sounds like the tables all are defined the same. Instead it would be much easier to use a single table and make the table number a column in that table. You can then simply query the data by giving the number as parameter. Also, you can search over all item tables at once if you ever want to.

Good luck!

Posted 10-Feb-16 0:32am

E.F. Nijboer

Comments

tareksnow 10-Feb-16 7:58am

True, would work, but we have a special case that requires the above architecture.

E.F. Nijboer 10-Feb-16 9:56am

In another reply I read about tables with very much data. You should really consider other options like partitioning, which is supported by mysql, sqlserver and many others. Sharding is also supported by both and many others. This is the same concept but involving multiple database servers. Splitting tables manually really is a poor man's solution.