Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
thedudeabides123
Starting Member
1 Post |
 Posted - 2012-06-19 : 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)UNIONSELECT * FROM Payments_002 WHERE (CONDITIONS)...UNIONSELECT * 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
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-06-19 : 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. |
 |
|
|
|
|
|
Union Help - Wildcard maybe?