Specifying file groups in Select Into.

IN Select into we can not specify file group and hence always table was created primary file group.

Then we have to create clustered index on other filegroup to change filegroup of created tabled. 

This is time and resource consuming as it needs to move data from one filegroup to other filegroup.

But now in MS SQL 2017 we can specify file group while using select into.

Lets see how to do this.

We have two filegroups in database.

[PRIMARY] and [SAMPLE]

So I want to create table in SAMPLE file group.

Select * into newtable  on [SAMPLE] from sys.tables

This will create table in SAMPLE file group instead of PRIMARY.