Insert Select Summary

  • Inserting from a staging table (view).

    Insert into i_tbl(a,b,c,d,e,f,g,h,i...z)

    Select (a,b,c,d,e,f,g,h,i...z) from s_view

    Problem is fields 'a' thru 'g' are primary key on

    i_tble that stageing s_view will violate. I need to group first on key, while still getting all the pieces.

    I thought to use a self-joined derived table in the select to first summarize. I'm getting the syntax wrong, or not going about this the best way because I'm still getting violation errors.

     

    Thoughts?

  • you need to tell us what do you do for the multiple values of 'h' throught 'z', MIN, MAX,...etc?

    EXAMPLE:

    nsert into i_tbl(a,b,c,d,e,f,g,h,i...z)

    Select a,b,c,d,e,f,g, MAX(h),MAX(i)...MAX(z)

    from s_view

    group by a,b,c,d,e,f,g

    Cheers,


    * Noel

  • 1 - Condolences for having a 7 piece Primary Key. Shot the key creator, now.

    2 - There should be rules in place if you have multiples (max, sum, min, something) entries by primary key

    INSERT INTO i_tbl(a,b,c,d,e,f,g,h,i...z)

    SELECT a,b,c,d,e,f,g, max(h), sum(i), min(j), count(k)...

    FROM s_view

    GROUP BY a,b,c,d,e,f,g

    Or you may need the latest date

    INSERT INTO i_tbl(a,b,c,d,e,f,g,h,i...z)

    SELECT a,b,c,d,e,f,g, h, i,....

    FROM s_view

    INNER JOIN

    (SELECT a,b,c,d,e,f,g, max(h) maxHValue

    FROM s_view

    GROUP BY a,b,c,d,e,f,g

    ) mydat ON s_view.a=mydat.a and s_view.b = mydat.b etc...

    AND s_view.h = mydata.maxHValue

    GROUP BY a,b,c,d,e,f,g

    Good Luck

  • It's probably me..

    There are 28 fields to be inserted. 15 fields make up primary key. Only 2 fields are summed the rest are inserted as is. Basically an hrs/wages feed from new HR system. I'm staging the records because the need to be rolled up by the key.

    Key = empl , orgz, cc, cat, shft, per_end, tc_seq, time_cd, pay_gr, job_cd... field(15)

    Non-Key = per_end_yr, per_no, union_cd, union_stat, check_id...

    Sum = sum(hrs) , sum(wages)

    So out of 28 fields, I need to group by the 15 key fields to get the sum's of hours & wages and insert the rest, which are mostly default values.

    clear as mud?

     

  • Hi Randy

    Just a thought (from a recent thread)...check that your datatypes are EXACTLY the same between the SELECT from your source table, and your target temp table.

    Values which are unique from the SELECT may not be unique if they are truncated or rounded when inserted into the target.

    Cheers

    ChrisM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • "There are 28 fields to be inserted. 15 fields make up primary key. Only 2 fields are summed the rest are inserted as is."

    Maybe I didn't understand it correctly, but this does not make sense to me. It can work under certain conditions, but even then it is at least potentially dangerous. You have 11 columns which are not part of primary key, and want to insert them "as is"? What if there are 2 different values in one of these columns for the same key? Then you will be trying to insert two rows with the same key... I think you need to create some rules first, what to do in such cases - then you can think about how to write the query.

  • Back to square one.

    I'm pulling one of the original feeds from the archives. It appears all the other fields outside of the Key fields/summed fields are default values. I knew that and had new feed designed the same, however it looks like they are not defaulting 2 of them.

    I had to look past the first 12000 records to find them. Once thats changed I should be able to do a simple group on all fileds.

     

     

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply