How can I select from a table where the tablename is a variable? 4

[joelwenzel]

Hi,

I want to do something like

SELECT * FROM (SELECT tableName FROM MyTables WHERE userID =3)

tableName is a column in the table MyTables. tableName is the name of another table in the database. So what I want is to select from the data in the table whose name is tableName. What I don't want is to select from the data returned by the set (SELECT tableName FROM MyTables WHERE userID =3) which is what I am currently doing. Any ideas how I can do what I want? Or is it even possible?

 

[joelwenzel]

so, I was thinking, one way to do this would be to create a stored procedure that would first select the tablenames from MyTable, then create a string select statement joining the tablenames to the string. And then it would call execute to run the string as an SQL statement. This would return the desired rows....but I don't know if I would be able to select anything from them once returned.

 

[bborissov]

declare @tablename varchar(20)
declare @sql varchar(200)
SELECT @tablename = TableName FROM  MyTables WHERE  userID =3
SET @sql = 'SELECT * FROM '+@tablename
EXEC (@sql)

Borislav Borissov

 

[joelwenzel]

yeah....I was thinking of something like that. Seems to be the way to go I guess. My tables are too long so I think I will break them up this way. thanks for the syntax.

 

[gmmastros]

Joel, please be careful with this method (known as 'dynamic SQL'). With Dynamic SQL, you lose the advantage of query optimization re-use.

When you create a stored procedure, SQL Server determines the best query plan to use, and stores that query plan, so that when you call the stored procedure again, it will be faster. With dynamic SQL, the query plan must be determined before returning the data.

Under most circumstances, it probably won't make much difference, but if you have tables with millions of records, the time difference will probably be noticeable.

There are other permission related issues with dynamic sql. I encourage you to research this a little more before you decide to take this approach.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[joelwenzel]

yes...I vaguely remember learning about query plans a long time ago. What it really sucks that i ccan store the query plan. Unfortunately, I don't know how else to do this. If I use dynamic SQL, I can break up my main table into several smaller tables (each with less then a million rows). If I don't use dynamic SQL, then I have to keep my information in one big table (with up to 500 million rows).

 

[vongrunt]

How 'bout horizontal partitioning?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[joelwenzel]

I found this article on dynamic SQL in case anyone else is interested. It is a very good overview of the advantages and pitfalls.

http://www.sommarskog.se/dynamic_sql.html

I am aware of horizontal/vertical partitioning but I have never fully understood the advantages. Suppose that I choose to use vertical partitioning (so I divide my 500 million row table into several shorter ones, maybe 50 ten million row tables). Now I want to select information from the partitioned tables and I need a few records from each of those 50 tables. How can this be any faster then a 500 million row table?

 

[SQLDenis]

yes horozontal partitioning with partitioned views
Check constraints on tables
query plan should hit only the table referenced in the where clause if it's setup properly
BTW you can only have 256 tables referenced in the view
if you name the view the same as the original table name then no code changes have to be made
When you do an insert/update/delete against the view the check constraint wil ensure that the correct table's are accessed


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

> Suppose that I choose to use vertical partitioning (so I divide my 500 million row table into several shorter ones, maybe 50 ten million row tables). Now I want to select information from the partitioned tables and I need a few records from each of those 50 tables. How can this be any faster then a 500 million row table?

Vertical... horizontal... .

Think in terms "live" and "archive" data. How often you need to search over rows that were created 5 years ago? Even a year ago?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[joelwenzel]

Well, for the application I am creating, people are almost as likely to search through data from 5 years ago as data from yesterday.

 

[joelwenzel]

ok, I've now looked into partitioning a bit more and I understand it now...just had to find an example. My original plan was to separate my table into several smaller tables specific to a category ID (that was in my original huge table)...and then find some way to do a SELECT from @TableName (the answer for this was dynamic SQL). But now I've learned that I can still divide up my big table into several smaller categorized tables but just combine them with a partioned view (i think for my case, it makes more sense to partition them on category rather then date). Thanks to all who responded.