Post reply

Change datafile for a table on SQL7

  • October 20, 2004 at 1:47 am

    #152300

    Hello!

    I Want to move a table from one datafile to an other (In the same DB on SQL7)

    This is to easy for the oracle but I Don't Know if I can do this in sql

    Thanx!

  • October 21, 2004 at 4:50 pm

    #527556

    I assume you mean another filegroup?  If there all multiple files in the same filegroup the table will be spread across all of them.  To control filegroup placement you just have to add "ON <filegroup>" to CREATE TABLE, CREATE INDEX, and PRIMARY KEY statements.

    Method 1: Create a second table on the other filegroup (I'll assume its named SECONDARY), copy all the data to it, drop the first table and rename the second.

       CREATE TABLE b (....) ON SECONDARY

    Method 2: Create a clustered index on the other filegroup, the table will follow.

       CREATE CLUSTERED INDEX IX_a ON TABLE a (xyz) ON SECONDARY

    Even if you immediately drop the clustered index, the table will stay on the new filegroup.

    If the original table has complications (an existing clustered key or index, or has foreign keys), use Enterprise Manager to make the change.  It will handle dropping and recreating everything.

  • October 22, 2004 at 3:49 am

    #527614

    Thank You !!!!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply