\[code\]Create table tbl_test( col1 nvarchar(255), col2 nvarchar(255), sum1 int, sum2 int, sum3 int)Create table tbl_test2( col1 nvarchar(255), col2 nvarchar(255), sum1 int, sum2 int, sum3 int)Insert into tbl_test (col1, col2, sum1, sum2, sum3) select 'a','a', 1 , 1 ,1 union all select 'a','b', 2,2,2 union all select 'a','c', 3,3,3 Insert into tbl_test2 (col1, col2, sum1, sum2, sum3) select 'a','a', 1 , 1 ,1 union all select 'a','b', 2,2,2 union all select 'b','a', 3,3,3 \[/code\]/*
What I would like my procedure to do is obtain a new table tbl_result:\[code\]tbl_result: col1 | col2 | sum1 | sum2 | sum3 'a' 'a' 2 2 2 || 1 + 1 'a' 'b' 4 4 4 'a' 'c' 3 3 3 'b' 'a' 3 3 3\[/code\]*/--So I would like to have some kind of reunion between the 2 tables.--The method I found so far is :-- Step 1: find the common values\[code\]Update a set a.sum1 = a.sum1 + b.sum1, a.sum2 = a.sum2 + b.sum2, a.sum3 = a.sum3 + b.sum3 from tbl_test a join tbl_test2 b on a.col1 = b.col1 and a.col2 = b.col2\[/code\]-- Step 2: find the new values \[code\]Insert into tbl_test (col1, col2 ,sum1,sum2,sum3) select b.col1, b.col2, b.sum1, b.sum2, b.sum3 from tbl_test a right join tbl_test2 b on a.col1 = b.col1 and a.col2 = b.col2 where a.col1 is null and a.col2 is null select * from tbl_test\[/code\]Unfortunately this is not good enough. If my tables have 1.000.000 records a join is probably not an optimal solution. Any ideas?
What I would like my procedure to do is obtain a new table tbl_result:\[code\]tbl_result: col1 | col2 | sum1 | sum2 | sum3 'a' 'a' 2 2 2 || 1 + 1 'a' 'b' 4 4 4 'a' 'c' 3 3 3 'b' 'a' 3 3 3\[/code\]*/--So I would like to have some kind of reunion between the 2 tables.--The method I found so far is :-- Step 1: find the common values\[code\]Update a set a.sum1 = a.sum1 + b.sum1, a.sum2 = a.sum2 + b.sum2, a.sum3 = a.sum3 + b.sum3 from tbl_test a join tbl_test2 b on a.col1 = b.col1 and a.col2 = b.col2\[/code\]-- Step 2: find the new values \[code\]Insert into tbl_test (col1, col2 ,sum1,sum2,sum3) select b.col1, b.col2, b.sum1, b.sum2, b.sum3 from tbl_test a right join tbl_test2 b on a.col1 = b.col1 and a.col2 = b.col2 where a.col1 is null and a.col2 is null select * from tbl_test\[/code\]Unfortunately this is not good enough. If my tables have 1.000.000 records a join is probably not an optimal solution. Any ideas?