Table Partition New data load part 7

How do we insert or load new data into partition table.

use Test_Tbl_Partition

select min(Date_Col),max(Date_Col) from [tbl_partition_test]

-- 2025-01-01 00:00:00.0000000	2025-01-15 00:00:00.0000000
select * from [tbl_partition_test]

-- create target table
CREATE TABLE tbl_partition_Staging (
ID Int identity NOT NULL,
Date_Col datetime2(7) NOT NULL,
 CONSTRAINT [PK_C1810429_tbl_partition_Staging] PRIMARY KEY CLUSTERED 
(
    [ID] ASC,
    [Date_Col] ASC
)
) ON [PRIMARY];


insert into tbl_partition_Staging ([Date_Col]) values ('2025-01-15 00:05:00.0000000')
insert into tbl_partition_Staging ([Date_Col]) values ('2025-01-15 00:06:00.0000000')
insert into tbl_partition_Staging ([Date_Col]) values ('2025-01-15 00:07:00.0000000')
insert into tbl_partition_Staging ([Date_Col]) values ('2025-01-15 00:08:00.0000000')
insert into tbl_partition_Staging ([Date_Col]) values ('2025-01-15 00:09:00.0000000')

select * from tbl_partition_Staging

--Get min date as new partition's split point(left bound)
SELECT  MIN(Date_Col) FROM tbl_partition_Staging

--Create new partition for new data
ALTER PARTITION SCHEME PS_myPartitionScheme     NEXT USED [Primary];
ALTER PARTITION FUNCTION PF_myDateRange ()  SPLIT RANGE('2025-01-15 00:05:00.0000000');

--drop CHECK if exists
ALTER TABLE tbl_partition_Staging DROP CONSTRAINT if exists ck_Min_Date_Col;

--ALTER TABLE tbl_partition_Staging DROP CONSTRAINT IF EXISTS ck_Min_Date_Col;
ALTER TABLE tbl_partition_Staging   WITH CHECK ADD CONSTRAINT ck_Min_Date_Col CHECK (Date_Col IS NOT NULL AND Date_Col >= '2025-01-15 00:05:00.0000000')

--Switch
ALTER TABLE tbl_partition_Staging SWITCH TO [tbl_partition_test] PARTITION $PARTITION.PF_myDateRange('2025-01-15 00:05:00.0000000');


--load and repeat the process for each new data
------------===================



insert into tbl_partition_Staging ([Date_Col]) values ('2025-01-16 00:10:00.0000000')
insert into tbl_partition_Staging ([Date_Col]) values ('2025-01-16 00:11:00.0000000')
insert into tbl_partition_Staging ([Date_Col]) values ('2025-01-16 00:12:00.0000000')
insert into tbl_partition_Staging ([Date_Col]) values ('2025-01-16 00:13:00.0000000')
insert into tbl_partition_Staging ([Date_Col]) values ('2025-01-16 00:14:00.0000000')

select * from tbl_partition_Staging


--Get min date as new partition's split point(left bound)
SELECT  MIN(Date_Col) FROM tbl_partition_Staging

--Create new partition for new data
ALTER PARTITION SCHEME PS_myPartitionScheme     NEXT USED [Primary];
ALTER PARTITION FUNCTION PF_myDateRange ()  SPLIT RANGE('2025-01-16 00:10:00.0000000');

--drop CHECK if exists
ALTER TABLE tbl_partition_Staging DROP CONSTRAINT ck_Min_Date_Col;

--ALTER TABLE tbl_partition_Staging DROP CONSTRAINT IF EXISTS ck_Min_Date_Col;

ALTER TABLE tbl_partition_Staging   WITH CHECK ADD CONSTRAINT ck_Min_Date_Col CHECK (Date_Col IS NOT NULL AND Date_Col >= '2025-01-16 00:10:00.0000000')

--Switch
ALTER TABLE tbl_partition_Staging SWITCH TO [tbl_partition_test] PARTITION $PARTITION.PF_myDateRange('2025-01-16 00:10:00.0000000');

--------====