SQL SERVER – SELECT INTO with FileGroup or Partitionis Not Possible

The other day, I received an email from user and after a long time before I answer the question, I had to check the answer online.

Here is the question -

I want to create a new table based from old table, but when I execute following script it gives me an error. Is there anything I am missing in my syntax?

SELECT * 
INTO NewTableName ON MyFileGroup
FROM MyOldTableName

I faintly remember that this was not possible in earlier version of SQL Server but I was not sure if this feature was added in the recent versions or not. I quickly tried few syntaxes and referred online documentation and learned that it is still not possible in the latest version of SQL Server.

The alternative is to just go ahead and change the default filegroup of any new table with following script.

Though, I do not like change the default filegroup for new tables. It is possible that when I have changed the default filegroup some other code executes behind the scene by automated system or my colleague, it will be also created on new filegroup.

ALTER DATABASE DatabaseName
MODIFY FILEGROUP NameofFileGroup DEFAULT

The reason this feature is not supported is that SELCT INTO is minimally logged operation. I seriously hope that some day in the future this feature get added in.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

One thought on “SQL SERVER – SELECT INTO with FileGroup or Partitionis Not Possible

  1. Thank Pinal.
    Possible steps to route data into required filegroup.

    Step 1) Create table with required filegroup
    Step 2) Then insert data

    Ex :
    CREATE TABLE [dbo].[Table1]
    ( [Column1][varchar](60) NOT NULL,
    [Column2] [varchar](80) NOT NULL,
    [Column3] [varchar](80) NOT NULL
    ) ON [fg_XYZ]
    go

    INSERT INTO dbo.Table1
    SELECT * FROM dbo.Table2 WITH (NOLOCK)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s