I have a sales table which I need to partition based on month and year of sales
table structure
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?
Best Answer
Firstly, it must be said that using SQL's native table partitioning support is preferred. But that requires SQL Enterprise, which is a lot of money, so, presumably, here we are.
A few things for you to check:
CHECK
constraintsales_1
andsales_2
overlap.WITH CHECK CHECK CONSTRAINT
). Given your definition in the question, it probably isn't the issue, but it is in general a necessary troubleshooting step (take a look atSELECT name, is_not_trusted FROM sys.check_constraints
)EDIT
Apparently
DATEPART
doesn't work. Probably you should just specify the dates directly (s_date >= '2014-01-01' AND s_date < '2014-06-01'
), etc. You could also use persisted custom columns.See: https://stackoverflow.com/questions/11155758/partitioned-view-not-working
Some other things to note about partitioned views, most of which I ran into quickly:
IDENTITY
columns will prevent inserts. What I did to get around this was create a "staging" table with the same structure (and an identity column), and add a trigger that just inserts into the partitioned view and deletes from the staging table. At some point I will look into using SQL 2012'sSEQUENCE
sINSERT
statement.DEFAULT
doesn't work or help. You can't omit nullable columns or columns with a default value.NOT FOR REPLICATION
), so you'll have to make sure that doesn't happenYou can see here at the bottom: http://technet.microsoft.com/en-us/library/aa933141(v=sql.80).aspx