November 9, 2010 at 9:04 am
I have seen 100's of examples of how to create a partitioned table but all of them are on new tables. How can I add a partition schemer to an existing table?
Many thanks
November 9, 2010 at 9:45 am
http://www.simple-talk.com/sql/database-administration/partitioned-tables-in-sql-server-2005/
You will need to create the function and partition scheme
I would do this by creating a new table then switching the partition with the old table to give a partitioned table and an empty table.
Once you've done that you can change the partitions.
Think it's all in the article above - just treat the old table as a partition to be switched in.
Cursors never.
DTS - only when needed and never to control.
November 10, 2010 at 5:10 am
Thanks for the reply but again the article was about new tables not exisisting tables.
But I did find a great article
Many thanks
October 4, 2011 at 9:36 am
I know this is an older topic, but I'd be interested in seeing how other people do this for larger tables. I often need to do this for multi hundred million row tables and I can't take the hit to my production system to drop and recreate the clustered index...what I've done in the past is to create an identical empty table with all the same schema, except that this table is on the partition. Then BCP out and in data from the source table one month at a time (month based partition). when I get to the current month, I have a process setup in which I do a last pull from the production table, insert the data into the staging table, then swap all the names (changing the active table name to something else ((_old)) and the newly partitioned table to the active table name), then running a clean up process to insert any trailing records which were inserted after I kicked off the final month BCP.
Their must be a better way of doing this.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply