Duplicate table and move it to different filegroup

Question

I need to duplicate atable (without copying data) and then move it to a different filegroup. Something like the following (which does not work for me).

SELECT * INTO NewTable ON [FG_newFilegroup] FROM oldTable WHERE 1=0 

Answer 1

You could change the default filegroup before the select into, and reset it after:

select 41 as i into newtable1
alter database test modify filegroup [secondary] default
select 41 as i into newtable2
alter database test modify filegroup [primary] default

select  t.name as TableName
,       f.name as Filegroup
from    sys.tables t
join    sys.indexes i
on      t.object_id = i.object_id
join    sys.filegroups f
on      f.data_space_id = i.data_space_id
where   t.name like 'newtable%'

This prints:

TableName   Filegroup
newtable1   PRIMARY
newtable2   SECONDARY

Answer 2

The SELECT INTO method does not replicate either constraints nor foreign keys nor indexes to the new table. To do this is a significant effort.

An alternate strategy is to move just the main data of the table by rebuilding the Clustered Index of the table on the destination filegroup.

Answer 3

You should write it this way:

INSERT INTO NewTable
SELECT * FROM oldTable WHERE 1 = 0

Note that the expression you wrote in your query 1 = 0 always false, therefore that causes no rows to be returned from the second SELECT statement. So this will lead to create an an empty table based on the columns in the SELECT clause of the query, and it won't copy the data from the second table into the new created one. To copy data as well you need to supply a truly expression in the WHERE clause, something like WHERE 1 = 1.

Answer 4

Use this to create a new table without the data:

SELECT TOP 0 * INTO NewTable FROM OldTable

Remove TOP 0 if you want the data as well. You can do whatever with the new table after that.