June 29, 2016 at 6:22 am
Hi,
what is the fastest and safest way to move a table (or all tables within a schema) of a File Group (primary) to another File Group (secondary - with 8 Files).
PS. I have a lot of Table to move :w00t:
Best Regards
Nicole 🙂
June 29, 2016 at 7:11 am
The usual way to do that is to rebuild the clustered index into the new filegroup ("CREATE INDEX WITH DROP_EXISTING" is one way to do that, if I remember correctly).
The other way is to Select Into. That allows you to change the schema and/or table name, if you want to. But it requires space to support two copies of the data during the operation.
If you need to move a lot of tables, work out how you're going to do it, then set up a cursor to step through them. You can do things like selecting from sys.tables where the schema_id is the one you want to move, or you can explicitly list the tables in a Table Value Constructor (search that if you aren't familiar with them).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 29, 2016 at 8:59 am
GSquared (6/29/2016)
The usual way to do that is to rebuild the clustered index into the new filegroup ("CREATE INDEX WITH DROP_EXISTING" is one way to do that, if I remember correctly).The other way is to Select Into. That allows you to change the schema and/or table name, if you want to. But it requires space to support two copies of the data during the operation.
If you need to move a lot of tables, work out how you're going to do it, then set up a cursor to step through them. You can do things like selecting from sys.tables where the schema_id is the one you want to move, or you can explicitly list the tables in a Table Value Constructor (search that if you aren't familiar with them).
I'd go with #1.
June 30, 2016 at 12:01 am
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply