--Use test db!
use test
--Cleanup!
drop table orders_load

--Create non-partitioned staging table
create table orders_load
(order_id int not null,
 order_date datetime not null,
 order_amount money default 0,
 order_details char(7000),
 CONSTRAINT pk_orderloadid PRIMARY KEY CLUSTERED (order_id),
 CONSTRAINT ck_orderload CHECK (order_id > 60000)) --Note the check constraint!

--Populate staging table
exec usp_loadstagedata

--SWITCH staging table into empty partition
ALTER TABLE orders_load
SWITCH TO orders partition 8

--Split out new partitions
ALTER PARTITION FUNCTION pf_orders()
SPLIT RANGE (70000)

--(Next used gets used, need a new next used)
ALTER PARTITION SCHEME ps_orders
NEXT USED [PRIMARY]

ALTER PARTITION FUNCTION pf_orders()
SPLIT RANGE (80000)

ALTER PARTITION SCHEME ps_orders
NEXT USED [PRIMARY]
