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)
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)
select * into targettable from old table
if you ask only structure we use below query
select * into taget from oldtable where 1=0