I have a sales table which I need to partition based on month and year of sales
S_ID numeric(18,0) S_CustID numeric(18,0) S_TotalCash numeric(18,0) S_Paid numeric(18,0) S_Date datetimeoffset(0)
I want to enter sales records between 01/01/2014 to 31/05/2014 to the first sales tables and from 01/06/2014 to 31/12/2014 to the second table and use a partitioned view to insert and select records from both the table so I add a constraint as
ALTER TABLE [dbo].[sales_1] WITH CHECK ADD CONSTRAINT [CK_sales_1_1] CHECK ((datepart(mm,[S_Date]) >= (01)) and (datepart(mm,[S_Date]) < (06)) and ((datepart(year,[date]) = (2014)))
for the first table and
ALTER TABLE [dbo].[sales_2] WITH CHECK ADD CONSTRAINT [CK_sales_2_1] CHECK ((datepart(mm,[S_Date]) >= (01)) and (datepart(mm,[S_Date]) < (06)) and ((datepart(year,[date]) = (2014)))
AND used a partitioned view
Select * from Sales_1 union all Select * from Sales_2
to insert and update records but when I try to insert data ssms shows error as
"UNION ALL view 'Test.dbo.Sales_View' is not updatable because a partitioning column was not found."
How to solve this?