I have a ~300 million row table with a column that has 9 different values. I need to be able to
SELECT INTO 9 different new tables based on this column.
At the moment it has to do 9 separate table scans, as I just run 9 separate queries. Is there a way I could do this in one table scan with a
CASE statement that defines the table to go into, or something similar?
I'm using SQL Server 2012 x64 Standard Edition SP1. There is no covering index as the column list is almost all of them. Example of what I'm trying to achieve:
SELECT [col1] ,[col2] ,[col3] ,[etc] INTO CASE WHEN col1=N'tbl1' THEN [dbo].[tbl1] WHEN col1=N'tbl2' THEN [dbo].[tbl2] END FROM [dbo][tblBase]
There is no clustered index on the base table. There is an index on
col1 but it chooses not to use it. Forcing it increases the cost dramatically as it does an RID Lookup.
The base table is 68GB, with SQL Server max server memory being 30GB (although that's increasing to ~50GB soon).