CREATE VIEW in SQL Server using UNION ALL

Question

Please consider the below example:

CREATE VIEW VW_YearlySales
AS

SELECT 2011 AS YearNo, ProductID, SUM(Amount) FROM InvoiceTable2011
UNION ALL
SELECT 2012 AS YearNo, ProductID, SUM(Amount) FROM InvoiceTable2012
UNION ALL
SELECT 2013 AS YearNo, ProductID, SUM(Amount) FROM InvoiceTable2013

GO

The InvoiceTable2013 doesn't exist actually and I don't want to create it right now, it will be created automatically when recording the first invoice for year 2013.

Can anyone help me on how to specify a condition that will verify the existence of the table before doing the UNION ALL?

Many thanks for your help.

Answer 1

As others have correctly said, you can't achieve this with a view, because the select statement has to reference a concrete set of tables - and if any of them don't exist, the query will fail to execute.

It seems to me like your problem is more fundamental. Clearly there should conceptually be exactly one InvoiceTable, with rows for different dates. Separating this out into different logical tables by year is presumably something that's been done for optimisation (unless the columns are different, which I very much doubt).

In this case, partitioning seems like the way to remedy this problem (partitioning large tables by year/quarter/month is the canonical example). This would let you have a single InvoiceTable logically, yet specify that SQL Server should store the data behind the scenes as if it were different tables split out by year. You get the best of both worlds - an accurate model, and fast performance - and this makes your view definition simple.