Post reply

Add Table to Different FileGroup

  • September 12, 2013 at 7:05 am

    #279697

    Hello Everyone

    I would like to create a new filegroup and add, for now, only one table, in the new filegroup for archiving purposes. I have not had but very little experience in doing this task.

    I have created the new file and filegroup in the database. The name of the FileGroup is MessageArchive. I would like to create a new table named MessageArchive in the New Filegroup named MessageArchive by using

    SELECT * INTO MessageArchive

    FROM Messages

    But I am missing something on how to define the filegroup that I want to use, which is MessageArchive.

    After all the data has been copied into the new table in the MessageArchive filegroup, how does one query from that table?

    Thank you in advance for your assistance, comments and suggestions.

    Andrew SQLDBA

  • September 12, 2013 at 7:25 am

    #1649707

    I just went the old slow route and created the table manually and now filling it via insert into statements.

    Thanks Any way

    Andrew SQLDBA

  • September 12, 2013 at 8:13 am

    #1649737

    SELECT INTO cannot specify a filegroup. The created table will be created on the default filegroup. If you want something else, you have to use the full, explicit CREATE TABLE syntax.

    As for how you query the newly created table, SELECT <column list> FROM <new table name>, nothing new, fancy or unexpected.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 12, 2013 at 8:46 am

    #1649756

    Thanks Gila

    That is exactly what I resorted too. I was hoping there was a simple way 😀

    I created the table manually on the other file group, and now inserting rows a little at a time.

    Andrew SQLDBA

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

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