July 11, 2007 at 11:32 am
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?
July 11, 2007 at 1:34 pm
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
July 11, 2007 at 1:40 pm
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
July 12, 2007 at 5:39 am
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?
July 12, 2007 at 6:12 am
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
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
July 12, 2007 at 7:37 am
"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.
July 12, 2007 at 8:27 am
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