creating a table in a specified file group
-
Hi,
i want to create a table as the structure of other table in a specified filegroup.
we have the option for creating a new table in a spcified filegroup,
'create table sample(x int) on FG_Data'
but i want to create the table by using select * into statement, as i need to create this table with the structure of other table, in a specified file group.
So please help me to find out the solution.
-
- you could set your new filegroup to be the default filegroup. Remark : all new objects will then reside in that default filegroup, unless you reset it afterward.
- you could execute your select ... into ... query and then put a clustering index on that new table and place that clustering index in your new filegroup.
If someone drops that index afterward, your heap will reside in the default filegroup at creation time of the table !
- you could also split your action in 3 steps
1) select top 1 * into yournewtable from yourquery
2) alter your new table to reside in the new filegroup (using EM)
3) perform an insert into yournewtable select * from yourquery
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thank you for the given solution.
But is there any direct command like
'create table sample ...on <filegroup>'.... for select...into...statement.
-
no.
btw 1) select top 1 * into yournewtable from yourquery
should be
1) select top 1 * into yournewtable from yourquery where 0=1
because you jus want to create the table without any data.
Maybe you're better of with creating a new db and positioning its files wherever you want.
use your_new_db
select *
into yournewtable
from your_old_db.owner.table ...
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
TanQ very much for ur prompting reply.I m using th first solution u have given me.
Using clustered index.
ThanQ.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply