Table Name Substitution with a variable

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.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: