insert multiple rows from select statement in sql

Question

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

Which rdbms? I think you could do with a mapping table or with a sub query embedded in the query that emulates a mapping table. — Insac
– Insac, Commented Oct 21, 2016 at 4:09
I think i use oracle RDBMS. If what I understand is correct. — Aimran Ramli
– Aimran Ramli, Commented Oct 21, 2016 at 9:10

Insac · Accepted Answer · 2016-11-20 17:14:03Z

1

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

edited Nov 20, 2016 at 17:14

answered Oct 21, 2016 at 4:21

Insac

8106 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Aimran Ramli Over a year ago

I dont really understand about mapping table. but I will try later. But my table consist or millions of query, will it effect the performance?

Insac Over a year ago

It might be (we'd have to see indexes and execution plan). However it's still just one access to tableA and one insert in tableB.

Aimran Ramli Over a year ago

tablemapping is bridge table? and what im trying to say earlier was. would it effect my performance because there is millions of data in table original. though i still haven't try your method and will get back to you as soon as possible

Insac Over a year ago

Yes, "tablemapping" is just the name I gave to the bridge table.For the performance, that's the only way I could find when your big table has to be read just once.

Aimran Ramli Over a year ago

first of all sorry for the lateness, I recently change workplace. thank you for the follow up, much appreciated. when I follow exactly your query, it returns 0 row inserted. so I changed select sequence1.nextval, ID, FieldTo to select sequence1.nextval, ID, type and change a.Id=m.fieldFrom to a.type=m.fieldFrom the result came as all the type = BOTH is enter as | B4 | A4 | both | | B5 | A4 | both | when it should be | B4 | A4 | Aa | | B5 | A4 | Bb | did I query your sql wrongly?

|

Kiran · Accepted Answer · 2016-10-21 05:27:25Z

0

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.

answered Oct 21, 2016 at 5:27

Kiran

1,1691 gold badge11 silver badges19 bronze badges

7 Comments

Insac Over a year ago

I'm a bit worried about the two calls to the sequence. It is going to generate twice the same value for the same record?

Aimran Ramli Over a year ago

I tried this query. I guess @Insac is right. It is going to generate twice

Aimran Ramli Over a year ago

with error SQL Error: ORA-02287: sequence number not allowed here

Kiran Over a year ago

you can use ROW_NUMBER() OVER(ORDER BY ID) instead of sequence

Aimran Ramli Over a year ago

wouldn't it add start from 1 everytime run the query?

|

Collectives™ on Stack Overflow

insert multiple rows from select statement in sql

2 Answers 2

9 Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related