July 29, 2010 at 9:38 am
Hi, I have a very large table that we have partitioned by fiscal year. Every day, in production, this table gets inserts with new data for the current fiscal period.
My question is: I am trying to refresh our development enviornment with current data from production. I'm wondering how do I go about refreshing the development table when its partitioned? I only want to bring over the current fiscal period but there is already current fiscal period data in the development table.
Do I just do a delete from {table} where fiscal year = 2010 and then do an insert from the other table where fiscal year = 2010? Or is there a more efficient and faster way to do this?
The current fiscal period has about 30 million records in it. I kicked off the delete statement in development about 18 minutes ago and its still running. I was thinking it would be faster because of the partitioning...
I am new to table partitioning so forgive my ignorance. Thanks. 🙂
July 29, 2010 at 11:48 am
Ok, I solved the problem myself. 🙂 I had to do a lot of creative googling but I finally figured it out...
I had to create a dummy table exactly like the partitioned one. Disable all the non clustered indexes on the original table. Create a clustered index on the dummy table to match the original. Then, run an ALTER TABLE...SWITCH statement to move the data from my partition to the dummy table. Then delete the dummy table. Now I can refresh the data from production. 🙂
What a pain in da butt! 😀 Why can't there just be a TRUNCATE TABLE <name> ON PARITITION #16 statement.... lol.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply