mySQL :: insert into table, data from another table?

INSERT INTO action_2_members (campaign_id, mobile, vote, vote_date)  
SELECT campaign_id, from_number, received_msg, date_received
  FROM `received_txts`
 WHERE `campaign_id` = '8'

---

for whole row

insert into xyz select * from xyz2 where id="1";

for selected column

insert into xyz(t_id,v_id,f_name) select t_id,v_id,f_name from xyz2 where id="1";

---

Answered by zerkms is the correct method. But, if someone looking to insert more extra column in the table then you can get it from the following:

INSERT INTO action_2_members (`campaign_id`, `mobile`, `email`, `vote`, `vote_date`, `current_time`)
SELECT `campaign_id`, `from_number`, 'example@domain.xyz', `received_msg`, `date_received`, 1502309889 FROM `received_txts` WHERE `campaign_id` = '8'

In the above query, there are 2 extra columns named email & current_time.

---

INSERT INTO Table1 SELECT * FROM Table2

---

INSERT INTO preliminary_image (style_id,pre_image_status,file_extension,reviewer_id,
uploader_id,is_deleted,last_updated) 

SELECT '4827499',pre_image_status,file_extension,reviewer_id,
uploader_id,'0',last_updated FROM preliminary_image WHERE style_id=4827488

Analysis

We can use above query if we want to copy data from one table to another table in mysql

Here source and destination table are same, we can use different tables also. Few columns we are not copying like style_id and is_deleted so we selected them hard coded from another table Table we used in source also contains auto increment field so we left that column and it get inserted automatically with execution of query.

Execution results

1 queries executed, 1 success, 0 errors, 0 warnings

Query: insert into preliminary_image (style_id,pre_image_status,file_extension,reviewer_id,uploader_id,is_deleted,last_updated) select ...

5 row(s) affected

Execution Time : 0.385 sec Transfer Time : 0 sec Total Time : 0.386 sec

---

This query is for add data from one table to another table using foreign key

let qry = "INSERT INTO `tb_customer_master` (`My_Referral_Code`, `City_Id`, `Cust_Name`, `Reg_Date_Time`, `Mobile_Number`, `Email_Id`, `Gender`, `Cust_Age`, `Profile_Image`, `Token`, `App_Type`, `Refer_By_Referral_Code`, `Status`) values ('" + randomstring.generate(7) + "', '" + req.body.City_Id + "', '" + req.body.Cust_Name + "', '" + req.body.Reg_Date_Time + "','" + req.body.Mobile_Number + "','" + req.body.Email_Id + "','" + req.body.Gender + "','" + req.body.Cust_Age + "','" + req.body.Profile_Image + "','" + req.body.Token + "','" + req.body.App_Type + "','" + req.body.Refer_By_Referral_Code + "','" + req.body.Status + "')";
                        connection.query(qry, (err, rows) => {
                            if (err) { res.send(err) } else {
                                let insert = "INSERT INTO `tb_customer_and_transaction_master` (`Cust_Id`)values ('" + rows.insertId + "')";
                                connection.query(insert, (err) => {
                                    if (err) {
                                        res.json(err)
                                    } else {
                                        res.json("Customer added")
                                    }
                                })
                            }
    
    
                        })
                    }
                }
    
            }
        })
    })

---

$insertdata="insert into partner_products(partner_id,partner_category_id,main_category_id, inventory_id,partner_product_name, partner_product_brand, partner_product_price,partner_product_quantity,partner_product_unit) select '123',partner_category_id,main_category_id,inventory_id, item_name,brand_name,item_price,item_qty, item_unit from inventory where partner_category_id='1'";

---