Dynamically UNION tables with similar names using SQL
We know it's not a good sign of database design to have multiple tables for the same data, but that's our case here anyway. Suppose we have this database where revenue tables are stored in multiple tables and we want to dynamically union these tables without writing a long select every time. This basically creates a variable that stores the text of the union all select , which gets the name of the tables dynamically from the sys tables then trim the last union all to make sure everything is alright you can print your query before executing it. and here everything seems alright. Replace PRINT with EXEC to execute your query and you are good to go.