Merge SQL multiple insert statement is not an option

Question

So, the continaution of another question...

I have merge which task is to create the junction table rows from an existing table which only can represent 1-1 connection between entities (PROJECT) and from a table which can represent N-1 connecton (worker) the junction table is (PROJECT-WORKER), everyproject should have a dedicated leaders, and every worker should have a project but now i should have an n-n connection... This merge would do the job:

But unfortunately, multiple matched then insert branch is not a "feature" in sql, how can i workout this problem. The original query---)

MERGE INTO WORKERPROJECT TARGET
USING (SELECT distinct
               w.worker_id,
               w.worker_type,
               w.project_id worker_project_id, 
               p.project_id project_project_id,
               p.dedicated_project_leader,
               p.dedicated_lead_developer,
               p.dedicated_lead_consultant,
               p.dedicated_supervisor
       from WORKER w 
       join PROJECT p on w.project_id = p.project_id
      ) SOURCE
   ON (SOURCE.worker_type is null)
 WHEN NOT MATCHED THEN INSERT (TARGET.FK_WORKER_ID, TARGET.FK_PROJECT_ID,TARGET.IS_ACTIVE,POSITION) 
  VALUES (SOURCE.WORKER_ID,SOURCE.worker_project_id,'ACTIVE',SOURCE.worker_type);
 WHEN NOT MATCHED THEN INSERT (FK_WORKER_ID, FK_PROJECT_ID,IS_ACTIVE,POSITION)
  VALUES (SOURCE.dedicated_project_leader,SOURCE.project_project_id,'ACTIVE','PROJECTVEZETŐ');
 WHEN NOT MATCHED THEN INSERT (FK_WORKER_ID, FK_PROJECT_ID,IS_ACTIVE,POSITION)  
  INSERT (FK_WORKER_ID, FK_PROJECT_ID,IS_ACTIVE,POSITION) VALUES (SOURCE.dedicated_lead_developer,SOURCE.project_project_id,'ACTIVE','FEJLESZTŐVEZETŐ');
 WHEN NOT MATCHED THEN INSERT (FK_WORKER_ID, FK_PROJECT_ID,IS_ACTIVE,POSITION)
  INSERT (FK_WORKER_ID, FK_PROJECT_ID,IS_ACTIVE,POSITION) VALUES (SOURCE.dedicated_lead_consultant,SOURCE.project_project_id,'ACTIVE','KONZULENSVEZETŐ');
 WHEN NOT MATCHED THEN INSERT (FK_WORKER_ID, FK_PROJECT_ID,IS_ACTIVE,POSITION)
  INSERT (FK_WORKER_ID, FK_PROJECT_ID,IS_ACTIVE,POSITION) VALUES (SOURCE.dedicated_supervisor,SOURCE.project_project_id,'ACTIVE','SUPERVISOR');

score 3 · Accepted Answer · answered Jan 22 '13 at 10:12

3

Actually you can. Thanks to the insert all http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9014.htm#i2095116

answered Jan 22 '13 at 10:12

Plouf

627
3
7

You made my day if it is true (y) – czupe Jan 22 '13 at 10:15
Well, I guess you can take your afternoon off then. ;) More seriously, each time when using Oracle you think "This is common problem, many people probably had it". Then, it already exists out of the box. Or, there is a very easy to find and well documented workaround (like identity columns) – Plouf Jan 22 '13 at 10:17
yeah, it should, but didn't find it : / anyway i have a feeling that insert all cant be used in merge statement : ( – czupe Jan 22 '13 at 10:23
@czupe - you can't combine `insert all` with `merge`, however in this case you don't need to - you're not actually updating the workerproject table. Replace your `merge` with an `insert all` driven from the select in `worker` – Chris Saxon Jan 22 '13 at 11:06
yeah, true, but i tried merge, and try understaind it better... So there is no way to multiple insert in merge... – czupe Jan 22 '13 at 11:12
But merge is not the appropriate statement in your case since you are not actually 'merging'. I mean, as a developer when you see merge you expect to see a `When MATCHED`and `WHEN NOT MATCHED`clause. Otherwise, it's just not the proper word, and it complicates the code. You wouldn't not use merge instead of a basic `INSERT, UPDATE, DELETE`, would you? Here it is the same: use insert all. The proper tool, for the proper job. ;) – Plouf Jan 22 '13 at 11:20
1

Ok, ok i am sold, i will accept it... But seriously it was only for my training issue and i can't find better example to represent that "What if i need multiple insert in my merge statement" – czupe Jan 22 '13 at 11:43
I would like to see how it looks like insert all query inside merge code – Nikolas Nov 28 '16 at 11:31

score 0 · Answer 2 · edited Sep 24 '19 at 14:46

0

If you need to perform multiple insert using merge then add the columns from different table into intial select. Something like this :

`MERGE INTO regional_dummy a 
USING (SELECT c.ID as IDNO FROM   region_country c    WHERE c.parent_id = '2' ) b
ON ('true' = 'false')
--WHEN MATCHED THEN UPDATE <TODO>
WHEN NOT MATCHED THEN
INSERT
(id,REGIONAL_userid,REGIONAL_NAME,REGIONAL_EMAIL,CREATE_DATE,CREATED_BY,REGION_ID,    COUNTRY_ID,PRODUCT_ID,ACTIVE,REPLACE_FLAG0)
VALUES    (REGIONAL_SEQ.nextval,'12345rg','userName','userEmailId',sysdate,  'rgrover0','2',b.IDNO,'m_product_region','Y' ,'N');`

edited Sep 24 '19 at 14:46

Chris Sim

4,054
4
29
36

answered Jun 19 '18 at 07:10

Rohit Grover

1
1

you can improve this answer by editing the code so it is formatted displayed nicely, having it all in one line makes it very hard to read – WhatsThePoint Jun 19 '18 at 07:33

Merge SQL multiple insert statement is not an option

2 Answers2