SQL Server: Partition table into multiple tables

Question

Can we use partition to split the table into multiple tables based on a key(multiple column values)?

Resultset: I have a temp table #results in my stored proc

ConId  ActNbr StageId  Qty   HoursInStage HoursPassed HourlyQty FlowedQty
------ ------ ------- ------ ------------ ----------- --------- ---------
6814     77     1     24000      24           0           NULL     NULL
6814     77     2     36000      19           5           NULL     NULL
6814     77     3     48000      15           9           NULL     NULL
6814     77     4     60000      11           13          NULL     NULL
6814     77     6     60000      24           0           NULL     NULL
6855     33     1     0          24           0           NULL     NULL
6855     33     2     15000      19           5           NULL     NULL
6855     33     3     15000      15           9           NULL     NULL
6855     33     4     15000      11           13          NULL     NULL
6855     33     6     20000      24           0           NULL     NULL
176892   10     1     0          24           0           NULL     NULL
176892   10     2     0          19           5           NULL     NULL
176892   10     3     0          15           9           NULL     NULL
176892   10     4     0          11           13          NULL     NULL
176892   10     6     0          24           0           NULL     NULL
176892   47     1     0          24           0           NULL     NULL
176892   47     2     0          19           5           NULL     NULL
176892   47     3     0          15           9           NULL     NULL
176892   47     4     0          11           13          NULL     NULL
176892   47     6     0          24           0           NULL     NULL

Wanted to partition the common rows that has same ConId and ActNbr. And insert the result rows into a new temp tables.

ConId  ActNbr StageId  Qty   HoursInStage HoursPassed HourlyQty  FlowedQty
------ ------ ------- ------ ------------ ----------- ---------  ---------
6814     77     1     24000      24           0           NULL      NULL
6814     77     2     36000      19           5           NULL      NULL
6814     77     3     48000      15           9           NULL      NULL
6814     77     4     60000      11           13          NULL      NULL
6814     77     6     60000      24           0           NULL      NULL

ConId  ActNbr StageId  Qty   HoursInStage HoursPassed HourlyQty  FlowedQty
------ ------ ------- ------ ------------ ----------- ---------  ---------
6855     33     1     0          24           0           NULL      NULL
6855     33     2     15000      19           5           NULL      NULL
6855     33     3     15000      15           9           NULL      NULL
6855     33     4     15000      11           13          NULL      NULL
6855     33     6     20000      24           0           NULL      NULL

-- splits other rows same as above two tables

I have tried to use partition, which ended up unsuccessful. Not sure if I'm moving in correct direction to achieve what I need.

Update on Comments: I mean Partition on table. The reason to split the #results table is for performing some math for individual split table that gets returned on final result set that has 24 hrs, each row for each hours and qty column that comes with some math on this partition tables.

I understood after searching and trying different methods doing math/logic is always better to carry out in further coding in my case java.

I've solved this on java using HashMap<Key,Value>. Key object that holds two columns(ConId and ActNbr) and Value as List of objects that has remaining columns.

But the question still remains if the table can be split based on a key(formed by table columns) and use this split records as regular table?

Answer 1

you can you use dynamic query and cursors together

--example data
SELECT *
INTO #temp
FROM (
SELECT 1 as Col1, 'X' as col2 , 'some other' as col3
UNION 
SELECT 1 as Col1, 'X' as col2 , 'some other 2' as col3
UNION
SELECT 1 as Col1, 'Y' as col2 , 'some other 3' as col3
UNION
SELECT 2 as Col1, 'X' as col2 , 'some other' as col3
UNION 
SELECT 2 as Col1, 'X' as col2 , 'some other 2' as col3
UNION
SELECT 2 as Col1, 'Y' as col2 , 'some other 3' as col3
) A



-- partitioning into multiple tables
declare  @table_ext int = 1;
declare  @col1 varchar(10);
declare  @col2 varchar(10);
declare @sql_st nvarchar(100);
declare cur cursor for select distinct col1 , col2 from #temp

open cur;

fetch next from cur into @col1 , @col2
while @@FETCH_STATUS = 0
begin
    set @sql_st = concat('select * into ##temp', @table_ext, ' from #temp' ,' where col1 = ''', @col1 ,'''  and col2 = ''' , @col2,'''')
    exec sp_executesql @sql_st
    fetch next from cur into @col1 , @col2;
    set @table_ext = @table_ext + 1
end
close cur;
deallocate cur;


-- test whether data stored correctly
select * from ##temp1
select * from ##temp2
select * from ##temp3
select * from ##temp4

Answer 2

You need to use dynamic sql with cursor to achieve the same.

DECLARE @CONID INT
DECLARE @ACTNBR INT
DECLARE DBCUR CURSOR FOR
SELECT DISTINCT ConId,  ActNbr FROM #RESULTS
OPEN DBCUR 
FETCH NEXT FROM DBCUR INTO @CONID, @ANTNBR
WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE @QUERY NVARCHAR(MAX)
SET @QUERY = 'SELECT * INTO #TMP' + CAST(@CONID AS VARCHAR(10)) + '_' + CAST(@ANTNBR AS VARCHAR(10)) + ' 
                FROM (SELECT * FROM YOURTABLE WHERE CONID =' + CAST(@CONID AS VARCHAR(10)) + ' AND ANTNBR =' + CAST(@ANTNBR AS VARCHAR(10)) + ') AS D;  '

EXEC SP_EXECUTESQL @QUERY

SET @QUERY = ' SELECT * FROM #TMP' + CAST(@CONID AS VARCHAR(10)) + '_' + CAST(@ANTNBR AS VARCHAR(10)) + ';   
               DROP TABLE #TMP' + CAST(@CONID AS VARCHAR(10)) + '_' + CAST(@ANTNBR AS VARCHAR(10)) + ''   ---- you may remove this line if want to use these temp table further 

EXEC SP_EXECUTESQL @QUERY


FETCH NEXT FROM DBCUR INTO @CONID, @ACTNBR
END
CLOSE DBCUR
DEALLOCATE DBCUR