Msg 4972, Level 16, State 1, Line 10
ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table 'Test.load.Header' allows values that are not allowed by check constraints or partition function on target table 'Test.dbo.Header'.

use [master]
go
if exists(select 1 from sys.databases where [name] = 'Test')
begin drop database [Test] end
go
create database [Test]
go
use [Test]
go
create schema load
go
-- Create partition scheme
CREATE PARTITION FUNCTION Pf_dbo_EndTransDateTime (DATETIME)
AS RANGE RIGHT
FOR VALUES ('20100706','20100707','20100708', '20100709','20100710','20100711', '20100712', '20100713', '20100714','20100715')
			
CREATE PARTITION SCHEME Ps_dbo_EndTransDateTime AS PARTITION Pf_dbo_EndTransDateTime ALL TO ([PRIMARY])

--select * from sys.partition_range_values
go
-- Create table on partition scheme
create table Header (TransactionID BIGINT not null, EndTransDateTime DATETIME not null, StoreNo INT, POSNo INT, TicketNo INT, CashierNo INT, InsertedDateTime DATETIME,
constraint pk_Header_TransactionID primary key (TransactionID, EndTransDateTime))on Ps_dbo_EndTransDateTime (EndTransDateTime)

alter table Header
add constraint uq_Header_NaturalKey unique (StoreNo, POSNo, TicketNo, EndTransDateTime) with (IGNORE_DUP_KEY = Off)

create index ix_Header_CashierNo on Header (CashierNo)
go
-- Create load table with unique index (to prevent dupes)-if violated, TranID will be set to 0 and child tables not inserted into
create table load.Header (TransactionID BIGINT not null, EndTransDateTime DATETIME not null, StoreNo INT, POSNo INT, TicketNo INT, CashierNo INT, InsertedDateTime DATETIME)on [PRIMARY]

alter table load.Header
add constraint uq_Header_NaturalKey unique (StoreNo, POSNo, TicketNo, EndTransDateTime) with (IGNORE_DUP_KEY = Off)
go

insert into load.Header
select 1, GETDATE(), 1,1,1,1,GETDATE()
go

-- Once load is complete, add additional indexes
alter table load.Header
add constraint pk_Header_TransactionID primary key (TransactionID, EndTransDateTime)

create index ix_Header_CashierNo on load.Header (CashierNo)

-- add check constraint to prevent dates other than today being inserted, and to ensure that the switch can be done
-- cannot perform the switch without the check constraint
declare @today datetime, @tomorrow datetime, @sql nvarchar(max)

select @today = CONVERT(char(8), current_timestamp,112), @tomorrow = dateadd(day,1,@today)  

--set @sql = 'alter table load.Header add constraint ck_Header_EndTransDateTime check (EndTransDateTime >= '''+convert(char(8),@today,112)+''' and EndTransDateTime < '''+convert(char(8),@tomorrow,112)+''')'
--select @sql
--exec sp_executesql @sql

alter table load.Header add constraint ck_Header_EndTransDateTime check (EndTransDateTime >= '20100714' and EndTransDateTime < '20100715')
go

-- perform switch of load table with partition in dbo
declare @date datetime, @boundaryid int

set @date = CONVERT(char(8), current_timestamp,112)

select @boundaryid = boundary_id from sys.partition_range_values
where value = @date

select @boundaryid

ALTER TABLE load.Header SWITCH TO dbo.Header PARTITION @boundaryid
go

use [master]
go
if exists(select 1 from sys.databases where [name] = 'Test')
begin drop database [Test] end
go
create database [Test]
go
use [Test]
go
create schema load
go
-- Create partition scheme
CREATE PARTITION FUNCTION Pf_dbo_EndTransDateTime (DATETIME)
AS RANGE RIGHT
FOR VALUES ('20100706','20100707','20100708', '20100709','20100710','20100711', '20100712', '20100713', '20100714','20100715')
			
CREATE PARTITION SCHEME Ps_dbo_EndTransDateTime AS PARTITION Pf_dbo_EndTransDateTime ALL TO ([PRIMARY])

select * from sys.partition_range_values
go
-- Create partition scheme for the load table
CREATE PARTITION FUNCTION Pf_load_EndTransDateTime (DATETIME)
AS RANGE RIGHT
FOR VALUES ('20100713', '20100714','20100715')
			
CREATE PARTITION SCHEME Ps_load_EndTransDateTime AS PARTITION Pf_load_EndTransDateTime ALL TO ([PRIMARY])

select * from sys.partition_range_values
go
-- Create table on partition scheme
create table Header (TransactionID BIGINT not null, EndTransDateTime DATETIME not null, StoreNo INT, POSNo INT, TicketNo INT, CashierNo INT, InsertedDateTime DATETIME,
constraint pk_Header_TransactionID primary key (TransactionID, EndTransDateTime))on Ps_dbo_EndTransDateTime (EndTransDateTime)

alter table Header
add constraint uq_Header_NaturalKey unique (StoreNo, POSNo, TicketNo, EndTransDateTime) with (IGNORE_DUP_KEY = Off)

create index ix_Header_CashierNo on Header (CashierNo)
go
-- Create load table with unique index (to prevent dupes)-if violated, TranID will be set to 0 and child tables not inserted into
create table load.Header (TransactionID BIGINT not null, EndTransDateTime DATETIME not null, StoreNo INT, POSNo INT, TicketNo INT, CashierNo INT, InsertedDateTime DATETIME)on Ps_load_EndTransDateTime (EndTransDateTime)

alter table load.Header
add constraint uq_Header_NaturalKey unique (StoreNo, POSNo, TicketNo, EndTransDateTime) with (IGNORE_DUP_KEY = Off)

-- add check constraint to prevent dates other than today being inserted, and to ensure that the switch can be done
-- cannot perform the switch without the check constraint
declare @today datetime, @tomorrow datetime, @sql nvarchar(max)

select @today = CONVERT(char(8), current_timestamp,112), @tomorrow = dateadd(day,1,@today)  

--set @sql = 'alter table load.Header add constraint ck_Header_EndTransDateTime check (EndTransDateTime >= '''+convert(char(8),@today,112)+''' and EndTransDateTime < '''+convert(char(8),@tomorrow,112)+''')'
--select @sql
--exec sp_executesql @sql

alter table load.Header add constraint ck_Header_EndTransDateTime check (EndTransDateTime >= '20100714' and EndTransDateTime < '20100715')

go

insert into load.Header
select 1, GETDATE(), 1,1,1,1,GETDATE()
go

-- Once load is complete, add additional indexes to make sure structure is identical to the target table
alter table load.Header
add constraint pk_Header_TransactionID primary key (TransactionID, EndTransDateTime)

create index ix_Header_CashierNo on load.Header (CashierNo)

go

-- perform switch of load (source) table with target (partitioned table in dbo schema)
declare @date datetime, @boundaryid_dbo int, @boundaryid_load int

set @date = CONVERT(char(8), current_timestamp,112)

select @boundaryid_dbo = boundary_id from sys.partition_range_values
where value = @date
and function_id = (select function_id from sys.partition_functions where name = 'Pf_dbo_EndTransDateTime')

select @boundaryid_load = boundary_id from sys.partition_range_values
where value = @date
and function_id = (select function_id from sys.partition_functions where name = 'Pf_load_EndTransDateTime')


select @boundaryid_dbo

select value  from sys.partition_range_values
where boundary_id = @boundaryid_dbo

ALTER TABLE load.Header SWITCH PARTITION @boundaryid_load TO dbo.Header PARTITION @boundaryid_dbo
go