Create table under a specific FileGroup WHERE 1=2

Question

I have an WinForms application which creates tables dynamically based on a given table such as:

SELECT * INTO TempTable FROM MyTable WHERE 1=2

I want those Temp tables to be created under a specific filegroup though using the above syntax.

The syntax to create the table under a filegroup is:

CREATE TABLE [dbo].[TempTable](

            [RECORDID] [numeric](10, 0) NOT NULL,
            --etc etc

) ON [TempFileGroup] TEXTIMAGE_ON [TempFileGroup]

Is it possible to use my syntax above to create the table under the specific filegroup?

I must be missing some simple detail here: when does 1 *ever* equal 2? — levelonehuman, Apr 12 '16 at 13:04
@ColinMackay Well dang, I'm either too young, too sheltered, or both to get the reference. Edit: I'm too sheltered. — levelonehuman, Apr 12 '16 at 13:05
SQL Server allows both table variables and temp tables. Why not simply use them? — Dan Bracuk, Apr 12 '16 at 13:13
@levelonehuman that syntax creates a new table (temp) based on your existing table (MyTable). It copies the same structure. — alwaysVBNET, Apr 12 '16 at 13:13
Persistent temp tables is an oxymoron. And if they are "temp" tables why do you really care which file group they belong to? — Sean Lange, Apr 12 '16 at 13:30
@SeanLange the system uses them to create perform multiple operations. We want to isolate those to a separate filegroup. — alwaysVBNET, Apr 12 '16 at 13:42
@alwaysVBNET I understand creating a temp table with `SELECT...INTO...`. What I don't understand is the `WHERE 1 = 2` portion. — levelonehuman, Apr 12 '16 at 13:44
@levelonehuman that is a well known method to generate the table with the same schema but leaving it empty. — Sean Lange, Apr 12 '16 at 13:52
If these temporary tables need to be isolated wouldn't it make more sense to create a scratch database so you don't have to worry about it? It still seems like a temp table should be sufficient but there may be more going on that we don't know about. It is a red flag to me if you have logic so complicated it requires multiple procedures hitting a persistent table that is filled with temp data. Sounds like there is too much business logic in the data layer. — Sean Lange, Apr 12 '16 at 13:56

Lukasz Szozda · Answer 1 · 2017-06-22T13:05:50.143

I want those Temp tables to be created under a specific filegroup though using the above syntax

From SQL Server 2017+ you could use ON filegroup syntax.

INTO clause

SELECT…INTO creates a new table in the default filegroup and inserts the resulting rows from the query into it.
[ INTO new_table ]
[ ON filegroup]
filegroup

Specifies the name of the filegroup in which new table will be created. The filegroup specified should exist on the database else the SQL Server engine throws an error. This option is only supported beginning with SQL Server 2017.

Example from MSDN:

Creating a new table as a copy of another table and loading it a specified filegroup

ALTER DATABASE [AdventureWorksDW2016] ADD FILEGROUP FG2;
ALTER DATABASE [AdventureWorksDW2016]
ADD FILE
(
NAME='FG2_Data',
FILENAME = '/var/opt/mssql/data/AdventureWorksDW2016_Data1.mdf'
)
TO FILEGROUP FG2;
GO
SELECT *  INTO [dbo].[FactResellerSalesXL] ON FG2 from [dbo].[FactResellerSales]

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

BOL states that

SELECT…INTO creates a new table in the default filegroup

(Emphasis added)

so unless you can find the table that was created and alter it with the argument syntax below, the newly created table is going to get created in the default filegroup.

MOVE TO { partition_scheme_name ( column_name [ 1, ... n] ) | filegroup | "default" }

Applies to: SQL Server 2008 through SQL Server 2016, SQL Database V12.

Specifies a location to move the data rows currently in the leaf level of the clustered index. The table is moved to the new location. This option applies only to constraints that create a clustered index.

Create table under a specific FileGroup WHERE 1=2

2 Answers2

Linked