| Author |
 Topic  |
|
|
thedudeabides123
Starting Member
USA
1 Posts |
 Posted - 06/19/2012 : 09:18:28
|
I have 20 different tables with identical columns. The tables are also named similarly. (Payments_001, Payments_002, ... , Payments_020)
I want to SELECT data from all of these tables. Currently the only way I know how to do that is to write 20 different SELECT statements and combine them with 19 UNION statements.
--------------------------------------------
SELECT * FROM Payments_001 WHERE (CONDITIONS)
UNION
SELECT * FROM Payments_002 WHERE (CONDITIONS)
.
.
.
UNION
SELECT * FROM Payments_020 WHERE (CONDITIONS)
---------------------------------------------
Please note, in the future there will be more tables added to this sequence and I want a query that will still work even when a Payments_021, Payments_022, etc. tables are added.
Is there anyway I can use a wildcard '%' to UNION all of these together in one statement. (ie LIKE 'Payments%' or something similar). Just seems like there should be an easier way than the way I am doing it. (New to SQL and using SQL Server 2008 by the way).
Thanks in advance. |
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 06/19/2012 : 09:34:44
|
T-SQL has no wild-card type of statement that can be used with a table name.
The only alternative I can think of is to use dynamic SQL. You would query for the names of the tables that match your criteria from sys.tables or INFORMATION_SCHEMA.Tables and use that to construct your query string that would look like the select you posted in your example. Then you can use sp_executesql to execute that query string.
Ideally, if you have any say in this, you should keep all the payments in a single table rather than Payments_001,002 etc. You might add additional columns to indicate whether the data belongs to 001, 002 etc. |
 |
|
| |
Topic |
|
|
|
Union Help - Wildcard maybe?
