Moving data between partitions in the same table

Question

Friends,

I have a requirement to move the data in a table between partitions. There would be 5 partitions

1 - Current month

2- previous month

3 - All the other months in current year

4 - previous year

5 - all the data before previous year..

We are not moving the old data to any other table. so the old data would be moved to the the first partition in this case...

I guess we have to include a staging table to achieve this.

Any inputs to implement this would be helpful...

---

Murali Krishnan

Answer 1

Ouch, you would have change the partition function every month.

Since one of the big wins with partitioning is that you can age out data with a quick meta data change, you don't get much of that. What do you hope to achieve with this scheme?

I almost get the feeling that a partitioned view would be easier to implement in this case.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

Yes Erland, you are right. We would usually archive out the data from the oldest partition. However, this is a wierd requirement where they want to have the data retained but moved along the partitions. I don see the purpose of using partitions in this case....

---

Murali Krishnan

Answer 3

Yes Erland, you are right. We would usually archive out the data from the oldest partition. However, this is a wierd requirement where they want to have the data retained but moved along the partitions. I don see the purpose of using partitions in this case....

I have actually suggested a customer a solution where they would partition a table and it would require moving the data, although in that case where would only be two partitions active and archived. The idea is that the archive partition would be on a read-only filegroup, and thereby backups could be reduced in size. They never came around to implement it as far as I know, and I will have to admit that I don't how well it would work in practice.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

Thanks Erland for your thoughts...We suggested our clients to use 3 partitions...one for current month, one for prev month and one for the records older than that.. Even here, we are not gonna archive the partition. However, we would have to merge and split only a month's data. I hope Merging one month's data would not be creating any issues...

---

Murali Krishnan