Working on some dynamic SQL to create a 13 month rolling partitioned table. See here for some background info.
The dynamic SQL part is simple enough, and the first partition switches out without a problem. Unfortunately when I get to the second partition I start getting this error:
Msg 4972, Level 16, State 1, Line 16
ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table 'DBName.dbo.TableName_Partition2' allows values that are not allowed by check constraints or partition function on target table 'DBName.dbo.TableName'.
I'm sure it's something simple but I can't quite figure it out. Here is the code.
-- Existing structure
USE [SourceDB]
GO
CREATE TABLE [dbo].[SourceTable](
[Col1] [bigint] NULL,
[Col2] [int] NULL,
[Col3] [int] NULL,
[Col4] [int] NULL,
[Col5] [int] NULL,
[Col6] [money] NULL,
[Modified] [datetime] NULL,
[ModifiedBy] [varchar](50) NULL,
[ModifiedType] [char](1) NULL
);
CREATE CLUSTERED INDEX [SourceTable_Modified] ON [dbo].[SourceTable]
( [Modified] ASC )
GO
USE [DestinationDB]
GO
CREATE PARTITION FUNCTION [ThirteenMonthPartFunction](datetime) AS
RANGE RIGHT FOR VALUES
( N'2015.08.01',
N'2015.09.01',
N'2015.10.01',
N'2015.11.01',
N'2015.12.01',
N'2016.01.01',
N'2016.02.01',
N'2016.03.01',
N'2016.04.01',
N'2016.05.01',
N'2016.06.01',
N'2016.07.01',
N'2016.08.01'
);
CREATE PARTITION SCHEME [ThirteenMonthPartScheme]
AS PARTITION [ThirteenMonthPartFunction] ALL TO ([PRIMARY]);
CREATE TABLE [dbo].[DestinationTable](
[Col1] [bigint] NULL,
[Col2] [int] NULL,
[Col3] [int] NULL,
[Col4] [int] NULL,
[Col5] [int] NULL,
[Col6] [money] NULL,
[Modified] [datetime] NULL,
[ModifiedBy] [varchar](50) NULL,
[ModifiedType] [char](1) NULL
) ON ThirteenMonthPartScheme (Modified);
GO
CREATE CLUSTERED INDEX [DestinationTable_Modified] ON [dbo].[DestinationTable]
(
[Modified] ASC
) ON ThirteenMonthPartScheme (Modified);
GO
SELECT *
INTO [TableName_Partition2]
FROM SourceDB.dbo.[SourceTable]
WHERE Modified >= N'2015.08.01'
AND Modified < N'2015.09.01';
GO
CREATE CLUSTERED INDEX [TableName_Modified]
ON [dbo].[TableName_Partition2] ([Modified] ASC);
GO
ALTER TABLE [dbo].[TableName_Partition2]
ADD CONSTRAINT [CK_TableName_Partition2]
CHECK ([Modified] >= N'2015.08.01'
AND [Modified] < N'2015.09.01');
GO
ALTER TABLE [TableName_Partition2]
SWITCH TO [DestinationTable] PARTITION 2;
GO
DROP TABLE TableName_Partition2;
GO
I've tried each partition number, I've tried reducing the time range to a single date (ie modified = '2015.09.02') but still get the same error. I'm stumped.
Thanks for any help.
DATE
rather thanDATETIME
? Check the NULLability of theModified
column. Chances are theSELECT INTO
creates it as NULLable when it should not be. I have also seen this occur with constraints becoming untrusted. Check their status withSELECT OBJECTPROPERTY( OBJECT_ID('CK_TableName_Partition2'), 'CnstIsNotTrusted') CnstIsNotTrusted;
. Set them to trusted again if required. – wBob Jul 5 '16 at 23:08CompassAudit.dbo.[TableName]
and just[TableName]
the same table? DoesCompassAudit.dbo.[TableName]
use the partition function and scheme in the code above? – James Anderson Jul 6 '16 at 7:47SourceDB
. That's the initial source of the data. No. Straight up [TableName] is the destination DB. I'll make a note of that in the code too. – Kenneth Fisher Jul 6 '16 at 14:16CREATE
script for the destination table[TableName]
complete with it's indexes and constraints? – James Anderson Jul 6 '16 at 14:22