That is typically the most efficient way to create & populate a table - much more efficient than SELECT INTO.... You might experiment with creating indexes after the insert, but I have never found this to be beneficial.
If you are using SQL 2005 Enterprise, you could look at the native table partitioning. If you are using SQL Server 2000 you might want to investigate PARTITIONED VIEWS before continuing.
Ok - are you building this table every time you query it, or is it something you do nightly and you are happy to query out of date data?
Are you doing this because you expect performance problems or because you have experienced performance problems?
How many rows do you typically return for each query? Presumably these would be distributed amongst all the tables?
Did you read up on partitioned views?
i want to do it at the application level rather executing a exe file ..my temporary table structure may vary according to the selected criteria...so as pootl flump said i am trying out temporary tables or views..i am just reading what are advantages and disadvantages in both the things
I had some performance problems while i am working with mysql so i moved to this approach.
The maximum number of rows is 10 million.
i read the partitioned views it is very useful for me and it is giving the result quickly can i know there are any dis advantages dealing with views ? but i searched in the net it is said that getting the details from a view is costly ..how to improve the performance on this can i use indexed views ? as i need to query the table once it is been created.