use test
go

create procedure usp_loadpartitiondata
as
begin
SELECT top 60000
	IDENTITY(int,1,1) as order_id,
	convert(datetime,'1/1/2011') as order_date
INTO #orderload
FROM
	master.sys.objects a
	, master.dbo.sysobjects b
	, master.dbo.sysobjects c
order by order_id

update #orderload
set order_date = DATEADD(dd,order_id/30,order_date)

INSERT INTO orders(order_id,order_date)
select order_id,order_date
from #orderload

drop table #orderload

end

go

create procedure usp_loadstagedata
as
begin
SELECT top 20000
	IDENTITY(int,60001,1) as order_id,
	convert(datetime,'7/1/2011') as order_date
INTO #orderload
FROM
	master.sys.objects a
	, master.dbo.sysobjects b
	, master.dbo.sysobjects c
order by order_id

update #orderload
set order_date = DATEADD(dd,order_id/30,order_date)

INSERT INTO orders_load(order_id,order_date)
select order_id,order_date
from #orderload

drop table #orderload
end

go

create procedure usp_fragmentpartition
as
begin
select 
	*
into #createfrag
from
	orders
where 
	order_id between 20001 and 30000
	and order_id%5 = 0

delete from orders 
where order_id in (select order_id from #createfrag)

while exists (select order_id from #createfrag where order_id not in (select order_id from orders))
begin
insert into orders(order_id,order_date)
select 
	order_id,
	order_date
from #createfrag
where order_id = (select MAX(order_id) from #createfrag where order_id not in (select order_id from orders))

insert into orders(order_id,order_date)
select 
	order_id,
	order_date
from #createfrag
where order_id = (select MIN(order_id) from #createfrag where order_id not in (select order_id from orders))

end

drop table #createfrag
end
go