I have created two tables one is OrdersLatest and another is OrdersMarch. I want to switch data from the partitioned table to the staging table(OrdersMarch) on the same filegroup and vice versa.
It was working well for switching out but issue on switch in. My case SWITCH IN not working
It shows the below error which is indicating a constraint mismatch between the source and target table(OrdersMarch). I am not sure what should I change to solve this error.
I have checked in both the left and right partitioned, but the issue is the same. I set constraints on the staging table (OrdersMarch) on the partitioned column also but the error is the same. Anyone can solve this issue, where is the exact issue?
ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table 'TEST.dbo.OrdersMarch' allows values that are not allowed by check constraints or partition function on target table 'TEST.dbo.OrdersLatest'.
Partition Function
CREATE PARTITION FUNCTION OrdersLatestPartitionFunction (datetime)
AS RANGE RIGHT FOR VALUES (
'20180101',
'20190101',
'20200101'
);
GO
Partition Scheme
CREATE PARTITION SCHEME OrdersLatestPartitionScheme
AS PARTITION OrdersLatestPartitionFunction
TO (
OrdersLatestFg1,
OrdersLatestFg2,
OrdersLatestFg3,
OrdersLatestFg4
);
GO
Create Table
CREATE TABLE OrdersLatest (
OrderDate date NOT NULL,
OrderId int IDENTITY NOT NULL,
OrderDesc varchar(255) NOT NULL,
CONSTRAINT PKOrdersLatest PRIMARY KEY CLUSTERED(OrderDate, OrderId)
)
ON OrdersLatestPartitionScheme(OrderDate);
GO
Switch Target Table
CREATE TABLE OrdersMarch (
OrderDate date NOT NULL,
OrderId int IDENTITY NOT NULL,
OrderDesc varchar(255) NOT NULL,
CONSTRAINT PKOrdersMarch PRIMARY KEY CLUSTERED(OrderDate, OrderId)
)
ON OrdersLatestFg3;
GO
Data Insert
INSERT INTO OrdersLatest(OrderDate, OrderDesc) VALUES
('20190302', 'Cat food'),
('20190315', 'Water bowl'),
('20190318', 'Saddle for camel'),
('20190321', 'Dog biscuits'),
('20190328', 'Bigfoot shoes');
GO
Output After Insert
+-------------+-----------------+--------+
| Partition | Filegroup | Rows |
|-------------+-----------------+--------|
| 1 | OrdersLatestFg1 | 0 |
| 2 | OrdersLatestFg2 | 0 |
| 3 | OrdersLatestFg3 | 5 |
| 4 | OrdersLatestFg4 | 0 |
+-------------+-----------------+--------+
Switch Out
ALTER TABLE OrdersLatest SWITCH PARTITION 3 TO OrdersMarch;
Output After Switch
+-------------+-----------------+--------+
| Partition | Filegroup | Rows |
|-------------+-----------------+--------|
| 1 | OrdersLatestFg1 | 0 |
| 2 | OrdersLatestFg2 | 0 |
| 3 | OrdersLatestFg3 | 0 |
| 4 | OrdersLatestFg4 | 0 |
+-------------+-----------------+--------+
Switch In
ALTER TABLE OrdersMarch SWITCH TO OrdersLatest PARTITION 3;
ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table 'TEST.dbo.OrdersMarch' allows values that are not allowed by check constraints or partition function on target table 'TEST.dbo.OrdersLatest'.