insert multiple rows into 1

  • Good day,

    I have a staging table, mc_staging, that has 6 columns (item, branch, month, source, inv_value, cme_value)

    Each item has 2 values for each branch, 1 inv_value and 1 cme_value- therefore there are 2 rows for each distinct record.

    I want to insert this data into another table, but i want these items to have both the inv_value and cme_value on the same row. i want to end up with 1 row for each item for each branch, with both the inv and cme values.

    How can i accomplish this??

  • I don't know if I understand you correctly but I think you could get the records combined by using something like this

    INSERT INTO realtable

    (

    ...your fields....

    )

    SELECT

    item,

    branch,

    month,

    source

    SUM(inv_value),

    SUM(cme_value) 

    FROM mc_staging

    WHERE ...your condition...

    GROUP BY item, branch, month, source

     

    ---------------------------------------------
    [font="Verdana"]Nothing is impossible.
    It is just a matter of time and money.[/font]

  • Assuming that the inv_value and cme_value might not be numerical, you could also do it this way.  This is also assuming that the item column is distinct for each item (assuming there will be two records for each item, one for each value).

    INSERT INTO realtable

    (

    ...your fields....

    )

    SELECT

     t1.item,

     t1.branch,

     t1.month,

     t1.source

     t1.inv_value,

     t2.cme_value

    FROM

     mc_staging t1 INNER JOIN

     ms_staging t2 ON t1.item = t2.item

  • HI all,

    thanks for the responses. I came to the same conclusion and I actually managed to achieve my goal with the following script:

    INSERT INTO MC_TEST (itemnmbr, old_branch,[month], inv_VALUE, cme_value)

    SELECT a.itemnmbr, a.old_branch, a.[month], a.inv_VALUE, b.cme_value

    FROM MC_STAGING a inner join mc_staging b on

    a.itemnmbr = b.itemnmbr and

    a.old_branch = b.old_branch

    WHERE a.SOURCE = 'Invoice Qty' and

    b.source = 'Contribution Margin Equalized'

    Thanks for the posts anyway, at least it confirms that the way i was doing it was the right way.

    Later.

Viewing 4 posts - 1 through 4 (of 4 total)

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