Oracle Insert via Select from Multiple Tables Where One Table May Not Have a Row

Oracle Insert via Select from multiple tables where one table may not have a row

Outter joins don't work "as expected" in that case because you have explicitly told Oracle you only want data if that criteria on that table matches. In that scenario, the outter join is rendered useless.

A work-around

INSERT INTO account_type_standard 
  (account_type_Standard_id, tax_status_id, recipient_id) 
VALUES( 
  (SELECT account_type_standard_seq.nextval FROM DUAL),
  (SELECT tax_status_id FROM tax_status WHERE tax_status_code = ?), 
  (SELECT recipient_id FROM recipient WHERE recipient_code = ?)
)

[Edit]
If you expect multiple rows from a sub-select, you can add ROWNUM=1 to each where clause OR use an aggregate such as MAX or MIN. This of course may not be the best solution for all cases.

[Edit] Per comment,

  (SELECT account_type_standard_seq.nextval FROM DUAL),

can be just

  account_type_standard_seq.nextval,

Select from multiple tables, insert into another table Oracle SQL query

If (fld_id1,fld_id2) are tbl_2.col1 and tbl_3.col1, so you you have to use JOIN

INSERT INTO tbl_1 (fld_id1,fld_id2)
SELECT t2.col1, t3.col1
FROM tbl_2 t2 
INNER JOIN tbl_3 t3 ON "JOIN CONDITION" 
WHERE "t2.someCondi"
AND "t3.someCondi"

So, if you can't use JOIN you can try with Cartesian Product:

INSERT INTO tbl_1 (fld_id1,fld_id2)
SELECT t2.col1, t3.col1
FROM tbl_2 t2, tbl_3 t3
WHERE "t2.someCondi"
AND "t3.someCondi"

Insert data into one table from another table avoiding duplicates

OK - from your description, I understand table t2 is currently empty, and you want to copy the rows where id is in (1, 2, 4) from table t1 to table t2.

Why your code fails:

You seem to believe that the condition is applied to the first row in t1, it passes so it is inserted into t2, then the condition is applied to the second row in t1 (using what is already inserted in t2), etc. - and you don't understand why there is any attempt to insert ALL the rows from t1 into t2. Why doesn't the third row fail the WHERE clause?

Good question! The reason is that operations are done on a SET basis. The WHERE condition uses table t2 AS IT WAS before the INSERT operation began. So for ALL rows, the WHERE clause compares to an empty table t2.

How to fix this... Decide which id you want to add when there are duplicate names. For example, one way to get the result you said you wanted is to select MIN(id) for each name. Moreover, you still want to check if the name exists in t2 already (since you may do this again in the future, when t2 is already partially populated).

insert into t2 ( id, name )
    select   min(id), name
    from     t1
    where    name not in (select name from t2)
    group by name
;

Oracle INSERT into two tables in one query

Try to use from dual;, like this:

INSERT ALL
INTO table1
  (tid, date, title) values (s_tid, s_date, s_title)
INTO table2
  (tid, date, user, note) values (s_tid, s_date, s_user, s_note)
SELECT s_tid, s_date, s_title, s_user, s_note
FROM
( 
    SELECT 
        1 s_tid,
        '01-JAN-15' s_date,
        'title' s_title,
        'john' s_user,
        'test note' s_note
    FROM dual;
)

Oracle sql insert into multiple tables from with clause with restriction

Use conditional insert all like here:

create table a(x, y, z) as (select 0, 0, 0 from dual);
create table b(x, y, z) as (select 0, 0, 0 from dual);
create table c(x, y, z) as (select 0, 0, 0 from dual);

create table src(x, y, z) as (
    select 1, 1, 1 from dual union all
    select 2, 2, 2 from dual union all
    select 3, 3, 3 from dual );

---

insert all 
  when x = 1 then into a (x, y, z) values (x, y, z)
  when x = 2 then into b (x, y, z) values (x, y, z)
  when x = 3 then into c (x, y, z) values (x, y, z)
select * from src

---

Related Topics