CODEWITHSUNDEEP
mysql
Amirul Fahmi
Amirul Fahmi

Reputation: 299

MYSQL insert multiple row from select statement

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

enter image description here

After inserted into table it will be something like this:

enter image description here

Upvotes: 0

Views: 70

Answers (2)

Roy
Roy

Reputation: 26

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

Upvotes: 0

Au Nguyen
Au Nguyen

Reputation: 669

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

Upvotes: 1

Related Questions