ORACLE insertion from other table using TOAD does not align properly (Showing NULL)

Question

I have created new table name NEW_TABLE like

Create table NEW_TABLE 
(
    Col_1 VARCHAR(50), 
    Col_2_ VARCHAR(50),
    Col_3_ VARCHAR(50)
)

I am inserting value from OLD_TABLE like this way

INSERT INTO NEW_TABLE (Col_1)  
    SELECT Col_1
    FROM OLD_TABLE_A 
    WHERE Col_1 IS NOT NULL;

INSERT INTO NEW_TABLE (Col_2)  
    SELECT Col_1 
    FROM OLD_TABLE_B 
    WHERE Col_1 IS NOT NULL;

When I want to see the NEW_TABLE it show the data like this

Col_1    Col_2   
-----    -----
AA
BB
CC
         XX
         MM
         ZZ
         PP
         CC

I am getting NULL value at the start of Col_2.

I want this:

 Col_1    Col_2
 -----    -----
    AA       XX
    BB       MM
    CC       ZZ
             PP
             CC

I have to insert different column in different time separately.while inserting a column I do not want to consider other

Answer 1

insert creates new row. If you want to fill column2 values where column1 is already filled you need to use update or merge. But as mentioned in comments you need to know how to match column2 with column1. You haven't provided any join condition for the data so people are guessing what you need. Please post some sample data from tableA and tableB and how it should look in new_table.

I think you need something like:

step1:

INSERT INTO NEW_TABLE (Col_1)  
    SELECT Col_1
    FROM OLD_TABLE_A 
    WHERE Col_1 IS NOT NULL;

step2:

merge into NEW_TABLE n
using OLD_TABLE_B b
on (/*HERE PUT JOIN CONDITION*/)
when matched then update set n.col_2_ = b.col_1;

step3:

merge into NEW_TABLE n
using OLD_TABLE_C c
on (/*HERE PUT JOIN CONDITION*/)
when matched then update set n.col_3_ = c.col_1;

Answer 2

Since you stated in a comment that there is no relation between the columns, and that there are the same number of columns in old_table_a and old_table_b this will work. I broke it into steps to make following it easier.

First establish the original table with a WITH clause. Then with another WITH clause, add an ID column which is the row number. Finally SELECT, joining on the ID (uncomment the INSERT line at the top when you are satisfied with the results).

Note the "ID" is meaningless as a true ID and serves only to match rows one for one in each table. If these tables have different numbers of rows you will get unexpected results but it meets your requirements.

SQL> --insert into new_table(col_1, col_2)
SQL> -- Set up the original old table A
SQL> with old_table_a(col_1) as (
     select 'AA' from dual union
     select 'BB' from dual union
     select 'CC' from dual
   ),
   -- Add the id, which is the row_number
   ota_rn(id, col_1) as (
     select row_number() over (order by col_1) as id, col_1
     from old_table_a
   ),
   -- Set up the original old table B
   old_table_b(col_1) as (
     select 'XX' from dual union
     select 'YY' from dual union
     select 'ZZ' from dual
   ),
   -- Add the id, which is the row_number
   otb_rn(id, col_1) as (
     select row_number() over (order by col_1) as id, col_1
     from old_table_b
   )
   -- Now join on the ID (which is really meaningless)
   select a.col_1, b.col_1
   from   ota_rn a
          join otb_rn b
            on (a.id = b.id);

COL_1      COL_1
---------- ----------
AA         XX
BB         YY
CC         ZZ

SQL>

Update before I even post the answer: I see from subsequent comments as I was about to post that you want to allow for adding additional columns with perhaps differing numbers of rows, etc. That will call for UPDATING, not INSERTING and unless you use the fake row_number ID method I use above really makes no sense in a true relational table. In that case this answer will not meet your needs but I will leave it here in case you want to adapt it for your needs.

I suggest you reconsider your approach to your original problem as this path will take you down a dark hole. You will have unrelated attributes in a table which violates basic database design and makes selecting this data in the future problematic at best (how will you query results? I'm curious how you will use this table). Maybe you should take a step back and reconsider your approach and at least start with some properly normalized tables. What's the real issue your are trying to solve? I bet there is a better way.

Answer 3

The second INSERT should be UPDATE, something like:

UPDATE NEW_TABLE
   SET Col_2 = (SELECT Col_2
                  FROM OLD_TABLE
                 WHERE Col_1 = <selection value>
               )
 WHERE Col_1 = <selection value> ;

Answer 4

The basic answer is that you should

insert into NEW_TABLE (Col_1, Col_2)
    select OLD_TABLE_A.Col_1, OLD_TABLE_B.Col_2
        from OLD_TABLE_A, OLD_TABLE_B
        where OLD_TABLE_A.Col_1 is not null
            and OLD_TABLE_B.Col_2 is not null;

the problem is that you will then get

Col_1 Col_2
----- -----
AA    XX
AA    YY
AA    ZZ
BB    XX
BB    YY
BB    ZZ
CC    XX
CC    YY
CC    ZZ

now the question you need to answer (that's what Dimitry asked in his comment) is how do you decide that you do not want the AA,YY, AA,ZZ, BB,XX, BB,ZZ, CC,XX and CC,YY ? Once you have an answer to this you can augment the where condition to remove them.

Answer 5

select      min (case tab when 'A' then Col_1 end)  as Col_1
           ,min (case tab when 'B' then Col_1 end)  as Col_2

from        (           SELECT 'A' as tab ,rownum as rn ,Col_1 FROM OLD_TABLE_A 
            union all   SELECT 'B'        ,rownum       ,Col_1 FROM OLD_TABLE_B
            )

group by    rn 

order by    rn
;

OR

select      min (Col_1)  as Col_1
           ,min (Col_2)  as Col_2

from        (           SELECT 'A' as tab,rownum as rn,Col_1 ,null  as Col_2 FROM OLD_TABLE_A 
            union all   SELECT 'B'       ,rownum      ,null  ,Col_1          FROM OLD_TABLE_B
            )

group by    rn 

order by    rn
;

OR

select      a.Col_1     
           ,b.Col_1     as Col_2

from                    (SELECT rownum as rn,Col_1 FROM OLD_TABLE_A) a
            full join   (SELECT rownum as rn,Col_1 FROM OLD_TABLE_B) b
            on          b.rn = a.rn

order by    coalesce (a.rn,b.rn)
;

---

Results

+-------+-------+
| COL_1 | COL_2 |
+-------+-------+
| AA    | XX    |
+-------+-------+
| BB    | MM    |
+-------+-------+
| CC    | ZZ    |
+-------+-------+
|       | PP    |
+-------+-------+
|       | CC    |
+-------+-------+

Answer 6

The problem as I see it is:

• Fill any holes in Col_2 with one of each of the values from OLD_TABLE_B, when you've run out of holes then add new rows.
• Exactly the same technique should to fill Col_3 from OLD_TABLE_C, as so on. Ideally the initial Col_1 from OLD_TABLE_A should also be able to use the technique although it's a simple insert.
• If you end up with an OLD_TABLE_B_PART_2 this should be able to be run against Col_2 later with the same technique.

The solution needs the following parts:

• A MERGE statement as you need to do updates otherwise inserts.
• To use a single MERGE for each pass to update multiple rows, each row with different values, you need a unique way of identifying the row for the ON clause. With no unique column(s) / primary key you need to use the ROWID pseudo-column. This will be very efficient at targeting the row in the table when we get to the UPDATE clause as ROWID encodes the physical location of the row.
• You need all the rows from OLD_TABLE and as many matching rows from NEW_TABLE you can find with holes, so it's a LEFT OUTER JOIN. You could do some sort of UNION then aggregate the rows but this would need an often expensive GROUP BY and you many need to discard an unknown number of surplus rows from NEW_TABLE.
• To match a (potentially non-unique) row in the OLD_TABLE with a unique hole in the NEW_TABLE, both will need a temporary matching IDs. The ROWNUM pseudo-column does this and is cheap.

Given the above, the following statement should work:

MERGE INTO NEW_TABLE
USING
  ( SELECT Col_1, ntid
    FROM
      ( SELECT ROWNUM num, Col_1
        FROM OLD_TABLE_B 
        WHERE Col_1 IS NOT NULL
      ) ot 
        LEFT OUTER JOIN 
      ( SELECT ROWNUM num, ROWID ntid
        FROM NEW_TABLE
        WHERE Col_2 IS NULL
      ) nt ON nt.num=ot.num
  ) sel
ON (NEW_TABLE.ROWID=ntid)
WHEN MATCHED THEN
  UPDATE SET Col_2=sel.Col_1
WHEN NOT MATCHED THEN
  INSERT (Col_2) VALUES (sel.Col_1);   

Check the execution plan before using on big data tables. I've seen the optimiser (in Oracle 12c) use a MERGE or HASH join against the target (NEW_TABLE) for the update rather than a plain USER-ROWID access. In this case the workaround I have used was to force NESTED-LOOP joins i.e. add an optimisation hint to the MERGE at the start of the query, so MERGE /*+ use_nl(NEW_TABLE) */. You may also need to check how it does LEFT JOIN depending on your data.

Answer 7

Create table NEW_TABLE 
(
    Col_1 VARCHAR(5), 
    Col_2_ VARCHAR(5),
    Col_3_ VARCHAR(5)
);


Create table OLD_TABLE 
(
    Col_1 VARCHAR(5), 
    Col_2_ VARCHAR(5),
    Col_3_ VARCHAR(5)
);

insert into old_table values ('AA','XX', null);
insert into old_table values ('BB','MM', null);
insert into old_table values ('CC','ZZ', null);
insert into old_table values (null,'PP', 'YYY');
insert into old_table values (null,'CC', 'XXX');


select * from old_table;

COL_1 COL_2 COL_3

----- ----- -----
AA    XX         
BB    MM         
CC    ZZ         
      PP    YYY  
      CC    XXX  

alter table new_table add (position number);

.

MERGE INTO new_table D
   USING  (select rownum position, old_table.* from old_table where col_1 is not null) S
   ON (d.position = s.position)
   WHEN MATCHED THEN UPDATE SET D.Col_1 = S.Col_1
   WHEN NOT MATCHED THEN INSERT (d.position, D.Col_1)
     VALUES (s.position, S.Col_1);  

MERGE INTO new_table D
   USING  (select rownum position, old_table.* from old_table where col_2_ is not null) S
   ON (d.position = s.position)
   WHEN MATCHED THEN UPDATE SET D.Col_2_ = S.Col_2_
   WHEN NOT MATCHED THEN INSERT (d.position, D.Col_2_)
     VALUES (s.position,S.Col_2_);  

MERGE INTO new_table D
   USING  (select rownum position, old_table.* from old_table where col_3_ is not null) S
   ON (d.position = s.position)
   WHEN MATCHED THEN UPDATE SET D.Col_3_ = S.Col_3_
   WHEN NOT MATCHED THEN INSERT (d.position, D.Col_3_)
     VALUES (s.position, S.Col_3_);

select * from new_table order by position; 

COL_1 COL_2 COL_3   POSITION
----- ----- ----- ----------
AA    XX    YYY            1
BB    MM    XXX            2
CC    ZZ                   3
      PP                   4
      CC                   5

You can drop POSITION column from new_table after the operation if you wish.

Answer 8

run below query

 INSERT INTO NEW_TABLE (Col_1, Col_2)  
( SELECT Col_1, Col_2
    FROM OLD_TABLE_A 
    WHERE not (Col_1 IS NULL and Col_2 IS NULL))

Answer 9

You can't do that like your way.

TRY THIS

INSERT INTO NEW_TABLE (Col_1,COL_2)  
    SELECT A.Col_1,B.COL_1
    FROM OLD_TABLE_A A FULL OUTER JOIN OLD_TABLE_B B ON 1=1
    AND A.Col_1 IS NOT NULL
    AND B.Col_1 IS NOT NULL;