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

Question

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)?

score 446 · Accepted Answer · answered Nov 22 '10 at 02:04

446

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'

answered Nov 22 '10 at 02:04

zerkms

249,484
69
436
539

1

@InSane: 1) give an answer 2) correct the question formatting. Don't miss the order next time ;-) – zerkms Nov 22 '10 at 02:09
ha ha :-) yup..i have never managed to get my priorities sorted! :-D – Jagmag Nov 22 '10 at 02:13
1

Working! +1 Perfect and very fast! Thanks mate. Just had to remove brackets from SELECT fields... – Somebody Jan 30 '13 at 10:49
@zerkms; Will triggers work with this `INSERT INTO ... SELECT` statement? – haccks Feb 24 '16 at 06:06
2

@haccks I believe the same as if it was just a "normal" `INSERT` – zerkms Feb 24 '16 at 06:20
@zerkms; Yes, you are right. Actually I just wrote a trigger and tested it now and it worked as it was with normal `INSERT`. Thanks. But, still not sure whether doing this is correct or not. – haccks Feb 24 '16 at 06:33
it's worked like a charm and I also learn how to do insertion from a table to another table automatically using windows Task Scheduler and Batch file. Learn more: https://ddroid509ht.blogspot.com/ – DoctorDroid Haiti Apr 26 '19 at 12:36

score 40 · Answer 2 · answered Apr 17 '15 at 18:48

40

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 Apr 17 '15 at 18:48

Lokesh Deshmukh

728
5
13

1

The whole row approach fails if there's already an existing record with a matching primary key. – HotN Dec 14 '16 at 15:50
Did you find any solution? – Shivdhwaj Pandey Feb 10 '20 at 13:57

score 16 · Answer 3 · answered Aug 09 '17 at 20:22

16

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.

answered Aug 09 '17 at 20:22

IamMHussain

716
8
11

2

What if I wanted to insert three rows of data with the same values from the select but different values for the 'additional' columns added such as email and current_time? – xxstevenxo Feb 01 '18 at 02:55

score 7 · Answer 4 · answered Apr 02 '17 at 19:33

7

INSERT INTO Table1 SELECT * FROM Table2

answered Apr 02 '17 at 19:33

Mher Arsh

588
4
21

This is a very low quality answer. Plus, unlike the accepted answer, it doesn't even try to relate to information included in the question. – Mike Apr 23 '20 at 15:51

Amit Jain · Answer 5 · 2019-08-20T11:41:05.227

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

score 0 · Answer 6 · edited Jan 30 '21 at 07:45

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")
                                    }
                                })
                            }
    
    
                        })
                    }
                }
    
            }
        })
    })

Please don't post only code as an answer, but also provide an explanation of what your code does and how it solves the problem of the question. Answers with an explanation are usually more helpful and of better quality, and are more likely to attract upvotes. — Ran Marciano, Jan 30 '21 at 07:16

score 0 · Answer 7 · answered Sep 02 '21 at 11:59

0

$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'";

answered Sep 02 '21 at 11:59

Anupam Verma

49
1
3

1

Please explain your solution. Answers which do not have an explanation and are only code get flagged as low effort. – cursorrux Sep 02 '21 at 17:55
Please provide an explanation for your answer without just the code. It will help someone to learn from your answer, not to just copy it – S.Sachith Sep 03 '21 at 01:43

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

7 Answers7

Linked

Related