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 (https://blog.sqlauthority.com)

Previous Post
MySQL – Grouping by Multiple Columns to Single Column as A String
Next Post
SQL SERVER – What is Incremental Statistics? – Performance improvements in SQL Server 2014 – Part 1

Related Posts

No results found.

2 Comments. Leave new

  • 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)

    Reply
  • select * into targettable from old table
    if you ask only structure we use below query
    select * into taget from oldtable where 1=0

    Reply

Leave a Reply

Menu