CREATE VIEW in SQL Server using UNION ALL
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.
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.
No, according to my knowledge its not possible in view, you have to use Stored Procedure. In Stored Procedure you can validate table existance & based on the existance of that table you can change your SQL.
EDIT:
CREATE PROCEDURE GetYearlySales
AS
IF (EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'InvoiceTable2013'))
BEGIN
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
END
ELSE
BEGIN
SELECT 2011 AS YearNo, ProductID, SUM(Amount) FROM InvoiceTable2011
UNION ALL
SELECT 2012 AS YearNo, ProductID, SUM(Amount) FROM InvoiceTable2012
END
Looks like you want to have a table for every year and you want to ensure that you have the query for SP without modifying the SP. This is slightly risky , you will have to maintain the naming conventions all the time. In this case what you will have to do is query the informationschema tables for table_name like 'InvoiceTable%'. Get the records in a table and then loop through the records attaching the fixed SQL. And then execute the dynamic sql like its done here http://www.vishalseth.com/post/2008/07/10/Dynamic-SQL-sp_executesql.aspx
链接地址: http://www.djcxy.com/p/61250.html