Insert multiple rows in ORACLE by referring 2 tables

Question

I have 2 tables with data as below

Table 1: ProductTransaction

Product Id | TransactionId
1          | 90
1          | 91 
2          | 91 
2          | 93 

Table 2: ProductEnrollment

Product Id | EnrollmentId
1          | 31
1          | 32 
2          | 41 
2          | 43 

I have a new table called ProductEnrollmentTransaction and I have to insert rows by referring Table 1 and 2. First, get the Product Id from Table 2 then get TransactionId from table 1 and then insert into table 3. For an instance, Enrollment 31 and 32 are associated with Product Id 1 and Product Id is associated with TransactionId 90 and 91. So New table must have transactionId and EnrollmentId based on product Id mapping.

TransactionId | EnrollmentId
90             | 31 
91             | 31
90             | 32 
91             | 32
91             | 41
93             | 41
91             | 43
93             | 43

How to write an Insert query in Oracle (19C) by referring to 2 tables? This query must go to all the way to Production. Please help us.

Answer 1

Use INSERT INTO ... SELECT ... and INNER JOIN the two tables:

INSERT INTO ProductEnrollmentTransaction (TransactionId, EnrollmentId)
SELECT t.TransactionId,
       e.EnrollmentId
FROM   ProductTransaction t
       INNER JOIN ProductEnrollment e
       ON (t.ProductID = e.ProductID)

Which, for the sample data:

CREATE TABLE ProductTransaction (ProductId, TransactionId) AS
SELECT 1, 90 FROM DUAL UNION ALL
SELECT 1, 91 FROM DUAL UNION ALL
SELECT 2, 91 FROM DUAL UNION ALL
SELECT 2, 93 FROM DUAL;

CREATE TABLE ProductEnrollment (ProductId, EnrollmentId) AS
SELECT 1, 31 FROM DUAL UNION ALL
SELECT 1, 32 FROM DUAL UNION ALL
SELECT 2, 41 FROM DUAL UNION ALL
SELECT 2, 43 FROM DUAL;

CREATE TABLE ProductEnrollmentTransaction (
  TransactionId NUMBER,
  EnrollmentId  NUMBER
);

Then, after the INSERT, the table contains:

TRANSACTIONID	ENROLLMENTID
90	31
91	31
90	32
91	32
91	41
93	41
91	43
93	43

fiddle