Davide Mauri Blog

<o:p></o:p>

After the introduction given in the article 1, we now have all the information we need in order to import data in a fast and non-locking way into our partitioned table.

The scenario is the same as article 1. We what to re-import all our July orders into our database. The orders are a lot, and while we’re importing data we also want that any query made on our database won’t get locked.

We have partitioned the Orders table on the RequestDate column, creating one partition for every month. Our goal is to create a script that will make our work easier.

First of all here’s the idea we’ll follow:

1. Create a new table called NewOrders200507 that will contains orders to be imported into Order table. It must have the same schema (and clustered index) of Orders.
   This table needs to be created in the same filegroup where july orders are stored. You can find this information with the script given in article 1.
2. Load data into this table. Since the table is not related in any way with the Orders table, no locks will be issued on this table. Beware, anyway, of disk contention since you’re operating on a filegroup used by both.
3. Create a constraint on NewOrders200507 so that it will assure by definition the table only contains data of July.
   This is mandatory in order to be able to make a partition switch.
4. Create a new table called OldOrders200507, again with the same schema. This table will be used as a container for the orders that has to be replaced by new ones.
   Again the filegroup must be same of the one used to store July partition values of Orders table.
5. Switch the partition that contains July orders of Orders table to OldOrders200507 table.
6. Switch the table NewOrders200507 to the same partition used in the above line.
   That’s it! Data has been replaced!
7. Drop the two created support table if you don’t need anymore. 

Ok, that’s it. Any bad news? Unfortunately yes. If we just want to import data for another month we have to change manually all the references to the partition number and the filegroup used in the script.

Not this time anyway. Sqlcmd the new command tool has much more functionalities than the old osql. One of the biggest news is the possibility to use variables:

setvar: name value

And to use it we just have to call using this syntax: $(name).

Beware that, before running any script that uses this feature, if you’re inside the Management Studio, you have to turn the sqlcmd mode on. You can do this by clicking this icon on:

So, we can write this script:

:setvar month 200507

:setvar filegroup FG7

:setvar partition 7

set nocount on

go

-- 1

print 'Creating table [NewOrders$(month)] on filegroup $(filegroup)...'

go

if  exists (select * from sysobjects where id = object_id(N'[NewOrders$(month)]') and objectproperty(id, N'IsUsertable') = 1)

drop table [NewOrders$(month)]

go

create table [NewOrders$(month)] (

      OrderId int not null,

      Description varchar(10) not null,

      RequestDate datetime not null

) on $(filegroup)

go

print 'Done.'

go

print 'Loading data for month $(month)...'

go

-- 2

-- Load data sample

declare @i int

set @i = 0

while (@i < 1000) begin

insert into [NewOrders$(month)]

values (cast(rand() * 1000000 as int), 'A', cast('20050701' as datetime) + rand() * 30)

waitfor delay '00:00:00.050'

set @i = @i + 1

end

print 'Done.'

go

-- 3

print 'Creating check constraint on [NewOrders$(month)]...'

go

alter table [NewOrders$(month)]

with check

add constraint CK_PartitionCheck

check (RequestDate >= '$(month)01 00:00:00.000' and RequestDate <= dateadd(d, -1, dateadd(m, 1, cast('$(month)01 23:59:59.997' as datetime))))

print 'Done.'

go

-- 4

print 'Creating table [OldOrders$(month)] on filegroup $(filegroup)...'

go

if  exists (select * from sysobjects where id = object_id(N'[OldOrders$(month)]') and objectproperty(id, N'IsUsertable') = 1)

drop table [OldOrders$(month)]

go

create table [OldOrders$(month)] (

      OrderId int not null,

      Description varchar(10) not null,

      RequestDate datetime not null

) on $(filegroup)

go

print 'Done.'

go

-- 5

print 'Switching partition $(partition) of [Orders] to table [OldOrders$(month)]'

go

alter table [Orders]

switch partition $(partition) to [OldOrders$(month)]

go

print 'Done.'

go

-- 6

print 'Switching [NewOrders$(month)] to Orders on partition $(partition)...'

go

alter table [NewOrders$(month)]

switch to [Orders] partition $(partition)

print 'Done.'

go

-- 7

print 'Dropping table [NewOrders$(month)]...'

go

--drop table [NewOrders$(month)]

-- If you don't need it you can also do this:

--drop table [OldOrders$(month)]

print 'Done.'

go

print 'Finished.'

go