CODEWITHSUNDEEP
sqloracle-databaseselectinsert
Aimran Ramli
Aimran Ramli

Reputation: 13

insert multiple rows from select statement in sql

I have a table that need to be migrate to new table.

Table A migrates to Table B

Table A

| ID   | type |

| A1   |  A   |
| A2   |  B   |
| A3   |  A   |
| A4   | both |
| A5   |  A   |

and I hope the table B will look like this

Table B

| ID   | FKA  |  TYPE |

| B1   |  A1  |   Aa  |
| B2   |  A2  |   Bb  |
| B3   |  A3  |   Aa  |
| B4   |  A4  |   Aa  |
| B5   |  A4  |   Bb  |
| B6   |  A5  |   Aa  |

If you realized that if type is both, it will insert two times in table B from table A.

**FKA is the foreign key from table A

Currently I do 3 queries

query 1:

insert into tableB
select sequence1.nextVal, ID, 
(case
when type = 'A' then 'Aa'
when type = 'B' then 'Bb'
when type = 'both' then 'Aa'
else NULL
end
) from tableA

query 2

insert into tableB
select sequence1.nextVal, ID, 
(case
when type = 'both' then 'Bb'
else 'laterdelete'
end
) from tableA

query 3

delete from tableB where type = 'laterdelete'

thanks guys

Upvotes: 1

Views: 3508

Answers (2)

Insac
Insac

Reputation: 810

I assume the rdbms is Oracle. You might want to create a table (e.g. "tablemapping") with these values

 FieldFrom FieldTo
 A         Aa
 B         Bb
 both      Aa
 both      Bb

So you could do just:

 Insert into tableB
 select sequence1.nextval, ID, FieldTo
    FROM tableA a 
         join tablemapping m 
           on a.type=m.fieldFrom

If you don't want to have a mapping table you can simulate one. 

 Insert into tableb
 select sequence1.nextval, ID, FieldTo
    FROM tableA a 
         join (
               select 'both'  as FieldFrom,  'Ab'  as FieldTo from dual
                Union all
               select 'both'  as FieldFrom,  'Bb'  as FieldTo from dual
                Union all
               select 'A'  as FieldFrom,  'Aa'  as FieldTo from dual
                Union all
               select 'B'  as FieldFrom,  'Bb'  as FieldTo from dual
              ) tablemapping m 
           on a.type=m.fieldFrom

Upvotes: 1

Kiran
Kiran

Reputation: 1159

You can use union in your first query to achieve the desired result in tableB. Query would be as given below:

insert into tableB
select sequence1.nextVal, ID, 
(case
when type = 'A' then 'Aa'
when type = 'B' then 'Bb'
when type = 'both' then 'Aa'
else NULL
end
) from tableA
UNION
select sequence1.nextVal, ID, 
(case
when type = 'A' then 'Aa'
when type = 'B' then 'Bb'
when type = 'both' then 'Bb'
else NULL
end
) from tableA

The above query will insert Aa and Bb in tableB when type is both in tableA. Hope this helps.

Upvotes: 0

Related Questions