MYSQL insert multiple row from select statement

Question

I have results from select statement,but i need to insert the result into another table.How to archieve this on mysql or php?

Select statement :

SELECT a.*,MONTH(bd.tarikh) bulan,tahun
  FROM 
  (
    SELECT bl_ic_pesawah ic,bl_name name,bl_musim musim,bl_zon_id zon,bl_nettWeight nettWeight,
    CASE 
    WHEN bl_nettWeight BETWEEN be_rangef AND be_ranget THEN be_quantity 
    WHEN bl_nettWeight >=  be_morethan  THEN be_quantity_1
    WHEN bl_nettWeight =  be_morethan_sws  THEN be_quantity_sws
    ELSE NULL END AS layak,
    be.be_date_from date_from,be_date_to date_to
    FROM b_ledger
    LEFT JOIN b_entitle be ON bl_musim = be_season AND bl_zon_id = be_zid
    WHERE be.be_status='Buka' AND bl_id = 1 
  ) a
  LEFT JOIN b_date bd ON bd.tarikh BETWEEN a.date_from AND a.date_to
  GROUP BY MONTH(bd.tarikh),a.ic

After inserted into table it will be something like this:

You could use: Insert into `another_table` SELECT a.*, ....

Raul
– Raul

2019-10-25 04:22:03 +00:00
Commented Oct 25, 2019 at 4:22 — Raul
– Raul, Commented Oct 25, 2019 at 4:22

Au Nguyen · Accepted Answer · 2019-10-25 04:25:25Z

You could use select in insert query, With your task:

INSERT INTO other_table (ic ,name ,musim ,zon ,nettWeight ,layak ,date_from ,date_to)
SELECT a.ic ,a.name ,a.musim ,a.zon ,a.nettWeight ,a.layak ,a.date_from ,a.date_to
,MONTH(bd.tarikh) bulan,tahun
  FROM 
  (
    SELECT bl_ic_pesawah ic,bl_name name,bl_musim musim,bl_zon_id zon,bl_nettWeight nettWeight,
    CASE 
    WHEN bl_nettWeight BETWEEN be_rangef AND be_ranget THEN be_quantity 
    WHEN bl_nettWeight >=  be_morethan  THEN be_quantity_1
    WHEN bl_nettWeight =  be_morethan_sws  THEN be_quantity_sws
    ELSE NULL END AS layak,
    be.be_date_from date_from,be_date_to date_to
    FROM b_ledger
    LEFT JOIN b_entitle be ON bl_musim = be_season AND bl_zon_id = be_zid
    WHERE be.be_status='Buka' AND bl_id = 1 
  ) a
  LEFT JOIN b_date bd ON bd.tarikh BETWEEN a.date_from AND a.date_to
  GROUP BY MONTH(bd.tarikh),a.ic

Roy · Accepted Answer · 2019-10-25 04:25:39Z

0

Get the result into an array.. then opening another database connection to create a new table..then INSERT sql using new database_table_name

answered Oct 25, 2019 at 4:25

Roy

264 bronze badges

Collectives™ on Stack Overflow

MYSQL insert multiple row from select statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related