How to create a table in another FILEGROUP using SELECT INTO

Question: I am trying to run a bulk load with the SELECT INTO method. Normally when using the SELECT INTO method it is possible to automatically create the table. But is it possible to create the new table in another filegroup during the SELECT INTO process?

The reason is I don’t have enough disk space on the PRIMARY filegroup disk , therefore need to redirect the new table onto a new a new FILEGROUP set up on a new disk .

Answer: It is not possible to create a new table in a non default FILEGROUP as part of the SELECT INTO. The documentation states: “SELECT…INTO creates a new table in the default filegroup and inserts the resulting rows from the query into it”

But there are various workarounds to this restriction of the new table being created in the DEFAULT FILEGROUP. One I’ve used in similar situations you’ve described is the following pattern

Before you complete these actions, you’ll need to make sure you have your alternative FILEGROUP set up on a new drive using How to add a sql table to a different FILEGROUP

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION FILEGROUP_NEW_TABLE
GO
--make the target FILEGROUP the DEFAULT filegroup
ALTER DATABASE [myDB] MODIFY FILEGROUP [myfilegroup] DEFAULT
GO
--this SELECT INTO will create the new table in the target FILEGROUP but with no data
SELECT * INTO temp_new_table  from dbo.myDocs WHERE 1 =0
-- make the original filegroup DEFAULT again
GO
ALTER DATABASE [myDB] MODIFY FILEGROUP [PRIMARY] DEFAULT 
GO
COMMIT TRANSACTION FILEGROUP_NEW_TABLE
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO
--proceed with INSERT statement 
INSERT INTO temp_new_table WITH (TABLOCK)  SELECT * from dbo.myDocs

Read More on SQL FILEGROUP

SQL Server – Moving a Non Clustered Index to another filegroup ...

SQL Server – files and filegroups. Improving database performance