mySQL :: insert into table, data from another table?
SqlMysqlSql Problem Overview
I was wondering if there is a way to do this purely in sql:
q1 = SELECT campaign_id, from_number, received_msg, date_received
FROM `received_txts` WHERE `campaign_id` = '8';
INSERT INTO action_2_members (campaign_id, mobile, vote, vote_date)
VALUES(q1.campaign_id, q1.from_number, q1.received_msg, q1.date_received);
Note: q1 would return about 30k rows.
Is there any way to do what I am attempting above in straight sql? To just pull the data straight from one table (basically a raw data table) and insert into another table (basically a processed data table)?
Sql Solutions
Solution 1 - Sql
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'
Solution 2 - Sql
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";
Solution 3 - Sql
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`, '[email protected]', `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.
Solution 4 - Sql
INSERT INTO Table1 SELECT * FROM Table2
Solution 5 - Sql
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
Solution 6 - Sql
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")
}
})
}
})
}
}
}
})
})
Solution 7 - Sql
$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'";