Combine Queries using Union All Dynamically

I have three tables:

    Table A                  Table B                     Table C
 ______________            ______________             ______________
| Order | Year |          | Order | Year |           | Order | Year | 
+--------------+          +--------------+           +--------------+
| O1-17 | 2017 |          | O1-18 | 2018 |           | O1-19 | 2019 |
+--------------+          +--------------+           +--------------+

I combined those tables using UNION ALL.

SELECT Order,Year FROM [Table A]
UNION ALL
SELECT Order,Year FROM [Table B]
UNION ALL
SELECT Order,Year FROM [Table C]

My problem is, each year there is a new table to be added. One example is that Table D with corresponding record Order O1-20 and Year 2020. Instead of adding another set of UNION ALL, is there any other way to achieve the same result.

I’m using this to build a report.

Answer

You have 3 options that I can think of:

1) Manually update the union in your query each year – not ideal but probably better than option 2.

2) Use dynamic SQL to build the query and it can automatically build the query based on the date the query is run. Its a bit ugly thought and performance might not be great.

3) This would be my preferred option, run a regular maintenance task to populate a completely separate table, in a single database, with just the data required for the report.

---

Option 2 might look like:

declare @StartYear int = 2015, @EndYear int = datepart(year, getdate()), @sql nvarchar(max) = '', @Index int;
set @Index = @StartYear;

declare @Years table ([Name] varchar(128));

while @Index <= @EndYear begin
  insert into @Years ([Name])
    select 'BaseTableName' + convert(varchar, @Index);
  set @Index = @Index+1;
end

select @sql = @sql + case when len(@sql) > 0 then ' union all ' else '' end + 'select [Order], [Year] from ' + [Name]
from @Years

select @sql
--exec(@sql)