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

user8629294

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.

Frank Kulash

Hi,

You can do something like this:

INSERT INTO patient (pat_key, pat_name, pat_age, product_id) =
SELECT  p.pat_key, p.pat_name, p.pat_age
,	c.product_id
FROM	patient  p
JOIN	cross_ref c ON  c.pat_key    = p.pat_key
           	    AND c.product_id <> p.product_id
;

Of course, without sample tables, I can't test anything.

What should happen if a pat_key is in cross_ref but not in patient? What if cross_ref has a given patient, but not with the same product_id? If these things are possible, include examples and explanation when you post the sample data

Frank Kulash

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

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

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

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

Hi,

You can do something like this:

INSERT INTO patient (pat_key, pat_name, pat_age, product_id) =
SELECT  p.pat_key, p.pat_name, p.pat_age
,	c.product_id
FROM	patient  p
JOIN	cross_ref c ON  c.pat_key    = p.pat_key
           	    AND c.product_id <> p.product_id
;

Of course, without sample tables, I can't test anything.

What should happen if a pat_key is in cross_ref but not in patient? What if cross_ref has a given patient, but not with the same product_id? If these things are possible, include examples and explanation when you post the sample data

Frank Kulash

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.