Complex insert thru Stored Procedure

Question

Hi,

I have 1 table with row data (No Primary Ky). and Another with Primary Key.

In row data table, i have multiple records on 3 perameters. And other 10 Field with same value in all the records on these 3 pereameters.

Eg.

aa bb cc dd ee ff gg hh ii p1 p2  p3          V1 V2 V3 V4 

a1 b1 c1 d1 e1 f1 g1 h1 i1 zz 123 03-05-2009  5  2  3  4
a1 b1 c1 d1 e1 f1 g1 h1 i1 zz 123 03-05-2009  10 1  4  4

I want to insert in my table is like

aa bb cc dd ee ff gg hh ii p1 p2  p3          V1 V2 V3 V4 

a1 b1 c1 d1 e1 f1 g1 h1 i1 zz 123 03-05-2009  15 3  7  8

Only one record group by column p1,p2,p3...

Can any one suggest me how to write SP For this??

I have defined one cursor with columns p1,p2,p3.

First i m checking that These type of record exists in master or not? as in my master table Primary key is based on these perameters.

Then i have written query with gorup by clause...??

But is it right way of doing it???

Please guide..

Thanks In Advance..

Answer 1

Oy - no cursors here, friend. None needed. Try this:

INSERT MyDetailTable (aa, bb, cc, dd, ee, ff, gg, hh, ii, p1, p2, p3, V1, V2, V3, V4)
SELECT d.aa, d.bb, d.cc, d.dd, d.ee, d.ff, d.gg, d.hh, d.ii, d.p1, d.p2, d.p3, SUM(d.V1), SUM(d.V2), SUM(d.V3), SUM(d.V4)
FROM   MyDetailTable d
JOIN   MyMasterTable m ON d.p1 = m.p1
  AND  d.p2 = m.p2
  AND  d.p3 = m.p3
GROUP BY d.aa, d.bb, d.cc, d.dd, d.ee, d.ff, d.gg, d.hh, d.ii, d.p1, d.p2, d.p3

The JOIN only exists to verify that the PK exists in the master table (although you should be using a foreign key constraint to ensure that this is the case - so you don't need to check that a master record exists in every statement you run against the detail table). Grouping does just that - groups by the "common" fields for each row. Instead of grouping by aa, bb, cc.....p1, p2, p3, you could just group by p1, p2, p3 and use MAX(aa), MAX(bb),... if you prefer - it's really up to you.