July 23, 2011 at 11:14 am
Hi,
I use followig sql statement to create a partation table and i am getting error message in SWITCH .
CREATE PARTITION FUNCTION pf_year (int)
AS
RANGE LEFT FOR VALUES ( 1960, 1970, 1980, 1990, 2000 )
CREATE PARTITION SCHEME ps_year AS PARTITION pf_year ALL TO ([PRIMARY])
--table creation
create table dp_test1 ( col1 int default 99 ,col2 int,col3 int primary key ) on ps_year(col3)
create table dp_test1_clone ( col1 int default 99 ,col2 int,col3 int primary key)
--Insert Records
insert into dp_test1 values ( 1966,1966,1966 )
insert into dp_test1 values ( 1971,1971,1971 )
insert into dp_test1 values ( 1972,1972,1972 )
insert into dp_test1 values ( 1984,1984,1984 )
insert into dp_test1 values ( 1985,1985,1985 )
insert into dp_test1 values ( 1986,1986,1986 )
insert into dp_test1 values ( 1987,1987,1987 )
insert into dp_test1 values ( 1988,1988,1988 )
--Move 1980's records to empty table
alter table dp_test1 switch partition 4 to dp_test1_clone
--need the record back to orginal table
alter table dp_test1_clone switch to dp_test1 partition 4
Getting error message
"Msg 4982, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. Check constraints of source table 'testpart.dbo.dp_test1_clone' allow values that are not allowed by range defined by partition 4 on target table 'testpart.dbo.dp_test1"
How to solve this issue.
July 24, 2011 at 1:11 am
Hi,
I added the check constriants as a soluation in other articles...But not solve the issues.
Following conditions i added to the tables
ALTER TABLE dp_test1 WITH CHECK ADD CONSTRAINT [Chk] CHECK ((col3>=(0) AND col3 IS NOT NULL))
ALTER TABLE dp_test1_clone WITH CHECK ADD CONSTRAINT [Chk1] CHECK ((col3>=(0) AND col3 IS NOT NULL))
again i am getting same error message
July 27, 2011 at 11:27 am
you shouldn't add the check constraint to your partitioned table, but only to the table you use for switching.
The check constraint is needed to ensure, that your non-partitioned table only contains the values that are allowed in the partition you are switching it with. So if you are switching to a partition that contains values from 1970 to 1980, then that is exactly what your check constraint should check.
/Sjang
July 27, 2011 at 11:29 pm
Still my issue is not yet solved.
Is any one please suggest, what i have to do the SWITCH between one table to another table
November 23, 2016 at 3:34 am
Hi,
Though this is an Old thread, my answer might help someone.(I believe you might have found the solution sometime later you posted this thread).
to be precise to the error, it is stating that the values of that column is not with in the boundary value of the partition (4 here).
Lets come to your partition, it is designed for LEFT FOR VALUES and your fourth partition value is 1900, then the Check constraint you need to build (on the Staging table, not required on the partition table) should be like this
ALTER TABLE [Scheme].[dp_test1_clone] ADD CONSTRAINT ck_MinBoundaryValues CHECK (COL(3) > 1980)
GO
ALTER TABLE [Scheme].[dp_test1_clone] ADD CONSTRAINT ck_MinBoundaryValues CHECK (COL(3) <= 1990)
GO
because when you mention the boudary set to LEFT, the value will be greater than the previous boundary and less than or equal to the current boundary.
once you created this check constraint on the Staging table then you could be able to Swith data IN and also OUT between the partition and staging table very easily.
Hope this helps.
November 23, 2016 at 6:19 am
Dear Parabu,
Thank you for your details.
Your tips are a useful one.
Regards
Mathew
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply