How to copy one record become two record to another table?

Experts,

I want to change table schema. From table_a to a new schema table_b.

table_a
=======
table_a_id      BIGINT
origin_id          BIGINT
destination_id      BIGINT
point            INT


table_b
=======
table_b_id      BIGINT
place_id              BIGINT
point            INT

I want to save every record in table_a become two record each table_b (second record point will be negative). For example:

table_a(table_a_id, origin_id, destination_id, point)
1, 10, 20, 100
2, 10, 30, 150
3, 20, 10, 200

table_b(table_b_id, place_id, point)
1, 10, 100
1, 20, -100
2, 10, 150
2, 30, -150
3, 20, 200
3, 10, -200

How to do that, by using storedprocedure? variable?

Thank you very much.
LVL 6
tikusbalapAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
you can do like this:
insert into table_b ( table_b_id, place_id, point)
select table_a_id, origin_id, point from table_A
union all
select table_a_id, destination_id, -point from table_A

Open in new window

0
 
tikusbalapAuthor Commented:
Thanks. Works like a charm :)
0
All Courses

From novice to tech pro — start learning today.