Insert from multiple tables, multiple rows

I have been trying to solve my problem but could not find the answer. Oracle db.

I have table context with looks like:

| contextId | customer |
 ----------------------
|         1 | John     |
|         2 | David    |

I have another table setting:

| contextId | settingName | settingValue |
 ----------------------------------------
|         1 | Happiness   |            6 |
|         1 | Sadness     |            3 |

What I would like is to insert David in setting, while copying the settingName and settingValue of John (contextId = 1).

Result should look like:

| contextId | settingName | settingValue |
 ----------------------------------------
|         1 | Happiness   |            6 |
|         1 | Sadness     |            3 |
|         2 | Happiness   |            6 |
|         2 | Sadness     |            3 |

Thanks

Answer

If your requirement is to use the customer names ‘David’ and ‘John’, then you need a join:

INSERT INTO SETTING (CONTEXTID, SETTINGNAME, SETTINGVALUE)
  SELECT 
    (SELECT CONTEXTID FROM CONTEXT WHERE CUSTOMER = 'David'),
    s.SETTINGNAME,
    s.SETTINGVALUE
  FROM CONTEXT AS c INNER JOIN SETTING AS s 
  ON s.CONTEXTID = c.CONTEXTID
  WHERE c.CUSTOMER = 'John'