Question:
I have data that is broken into multiple tables depending on the month. For example:
99jan table99feb table
These tables are all built the same and are very large (each one has 500,000 rows).
I am writing a simple interactive script that prompts you “From what month do you want to see your query?” and accepts your answer into a variable. It then will pull information from the table you entered. How can I use a statement like:
'Select col1,col2 from '&variable' where ...
Answer:
Assuming you are using SQL Server, you can use “dynamic sql.” This means that you can build up a string that contains the command you want to run and then tell SQL Server to execute the string. For example:
use pubsgodeclare @string char(120), @table char(30)select @table = 'authors'select @string = 'select * from ' + @tableexec (@string)/* or you can even do this */exec ('select * from ' + @table)
However, I question the need to create separate tables for just 500,000 rows apiece on SQL Server. I’ve had tables with more than 10 million records perform quite well.
