UNION ALL

Question

Hi Folks
I have a number of Monthly tables that are of an identical structure. Each month a new table is  generated. These tables are located with a single "Events" database. I need to "UNION ALL" these tables but want to avoid hard coding the table "YearMonth" Suffix into the SQL statement

SELECT * 
FROM Events_201211
SELECT *
FROM Events_201212
UNION ALL
SELECT * 
FROM Events_201301

If I hard coded the YearMonth suffix I would need to update the Statement each month. i.e next month the code would need to be updated to 

SELECT * 
FROM Events_201211
SELECT *
FROM Events_201212
UNION ALL
SELECT * 
FROM Events_201301
SELECT * 
FROM Events_201302

I assume there is a easy way to avoid specifying each table name. Can someone suggest a solution?

Many thanks

Steve 

Answer 1

Your problem is in creating separate tables for each month. Can you tell why you don't want to keep information in one table?

For your scenario you will have to use dynamic SQL, which is not a good idea.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

As suggested by Naomin N, it is a bad idea to use dynamic SQL. However if you are desperate to look for the solution, this query should work.

DECLARE @MYQUERY AS VARCHAR(MAX)
SET @MYQUERY = ' '
SELECT @MYQUERY = @MYQUERY + ' SELECT * FROM ' + NAME + ' UNION ALL' FROM sys.tables WHERE TYPE = 'U' AND NAME LIKE 'Events_%'
SET @MYQUERY = LEFT(@MYQUERY,LEN(@MYQUERY) - 10)
EXEC (@MYQUERY)

---

Thanks & Regards,
Prasanna Vaitheswaran

---

Please mark the post as 'Answer', if it addresses or resolves your query. Please click on 'Vote as Helpful', if it is helpful.

Answer 3

Hi Naomi

Thanks for your reply. Each table contains a large volume of data. If all the tables were combined into a single table, it would be very large. 

99% of our day to day  queries  only pull data from the most recent months table. However, I have this requirement to combine data from all the tables once a month to run a report. The TSQL will run inside a SSIS connection manager  on a scheduled basis at night when query performance isn't a big issue (it doesn't matter if the query takes 1 minute or 15 minutes). The key requirement is more that I don't have to update the TSQL code each month.

Hope this additional information helps. Regards Steve  

Answer 4

Hi Naomi

Thanks for your reply. Each table contains a large volume of data. If all the tables were combined into a single table, it would be very large. 

99% of our day to day  queries  only pull data from the most recent months table. However, I have this requirement to combine data from all the tables once a month to run a report. The TSQL will run inside a SSIS connection manager  on a scheduled basis at night when query performance isn't a big issue (it doesn't matter if the query takes 1 minute or 15 minutes). The key requirement is more that I don't have to update the TSQL code each month.

Hope this additional information helps. Regards Steve  

Sounds like you need to look into table partitioning

---

Chuck Pedretti | Magenic – North Region | magenic.com

Answer 5

Yes, there is a simple solution: make the month a column and then make it a single table.

Later you say that Each table contains a large volume of data, but you don't say how much data that is. SQL Server is designed to work with lots of data, and if you only query the most recent data, it does not matter if there is lots of other data, as long as there are good indexes to match the queries.

When large amount of data usually causes problem is when you want to age out old data; this can be painful.

For this reason, SQL Server provides two means of partitioning: partitioned views and partitioned tables. The latter is only available in Enterprise Edition. Partitioned views are avilable in all versions of SQL Server.

I have some more text on partitioning on my web site: http://www.sommarskog.se/dynamic_sql.html#Sales_yymm

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 6

Hi Erland,

Thanks for the input.  I used the term "large volume"of data" rather than being specific as our data volumes are a bit of a moving target.  Currently the monthly tables have about 7 million rows, however it is projected that these monthly volumes could increase 10 X over the next year. 

Thanks for the link to you website. I will be doing research on partioning and indexes.

Cheers

Steve 

Answer 7

7 millions rows a month is not that frightening, assuming a normal row size. If you really get 70 millions rows per year, you certainly should consider Enterprise Edition, if you don't have it already.

---

Erland Sommarskog, SQL Server MVP, [e-mail]