insert multiple rows on table based on other table's column value — oracle-tech

    Forum Stats

  • 3,714,821 Users
  • 2,242,634 Discussions
  • 7,845,081 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

insert multiple rows on table based on other table's column value

user8629294
user8629294 Member Posts: 87 Red Ribbon

HI,

I am using oracle 11g release 11.2.0.4.0. I have a problem regarding inserting multiple rows into table based on other table's column value .Let me explain me bit more .

I have a table PATIENT and other table CROSS_REF AS mentioned below .

How to build insert statement from patient and cross ref so that i will get the desired output. I tried with merge but i could not accomplish.


Tagged:

Best Answer

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 39,951 Red Diamond
    edited January 25

    Hi, @user8629294

    Whenever you have a question, please post a little sample data in a usable form (such as CREATE TABLE and INSERT statements), so the people who want to help you car re-create the problem and test their ideas. Also post the exact results you want from the given data, and an explanation (in general terms) of how you get those results from that data.

    See: How to Ask Questions in Developer Community Spaces - oracle-tech

     I tried with merge but i could not accomplish.

    What was wrong with the MERGE statement you tried? It's hard to say what you did wrong without knowing what you did. Post the MERGE statement.

  • mathguy
    mathguy Member Posts: 9,463 Gold Crown

    Is this a production system, or something you are seeing in class (are you a student)?

    I ask because the PATIENT table suggest a bad data model. You have patients, identified by "patient key", with attributes "patient name" and "patient age". (That too is a bad thing: age changes every year; you should store "patient date of birth", and if age is needed, let that be calculated whenever needed, since it's not fixed over time.)

    To this, in your model, you want to add all the product id's cross-referenced to each patient. That is fine - in the cross-reference table. You should not have that kind of information in the PATIENT table.

    What makes more sense is to have a table like PATIENT, but without the last column, and a table like CROSS_REF. Then what you show as the "desired output" should be a view based on the two tables - it shouldn't be stored on disk.

    With all of this being said, I could easily show you an INSERT (or perhaps a MERGE) statement that does what you requested, but I won't - that would simply encourage you in the use of this bad data model. I won't be part of that.

    Good luck!

  • user8629294
    user8629294 Member Posts: 87 Red Ribbon

    merge into patient a

    using ( select cr.product_id,p.* from

    patient p ,

    cross_ref cr

    where p.pat_key = cr.pat_key

    ) b

    on ( a.pat_key = b.pat_key

    and a.product_id = b.product_id

    )

    when not matched then insert ( pat_key,pat_name,pat_age,product_id)values

    (b.pat_key,b.pat_name,b.pat_age,b.product_id);


    after doing this so many unwanted rows also got inserted .Plz guide me.

  • user8629294
    user8629294 Member Posts: 87 Red Ribbon

    Hi Mathguy,

    Its not part of a prod. I faced a problem like this so simulated the pblm using this example.

    My pblm was like i have to join a dimension table with fact table and insert those many rows depending on the number of drugs used by the patient in fact table .

    ods_raw_patient table has now one row for whatever no of drugs used by patient.

    But now i have to insert multiple rows into ods_raw_patient based on number of drugs used in the fact table i.e ods_shipment table.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 39,951 Red Diamond

    Hi, @user8629294

    after doing this so many unwanted rows also got inserted .Plz guide me.

    In the MERGE statement you posted, sub-query b has two columns called product_id. If you're getting unwanted rows (or wanted rows, for that matter), and not an error regarding the ambiguous column name, then that's not the statement you're actually running.

Sign In or Register to comment.