Stored procedure to Insert data into table with select statement and argument parameter

Question

I am creating a stored procedure which will populate my table with data passed from procedure argument and from other table.

I have a table with three columns (cart_id, product, sold_quantity). Now, I want to populate this table with a stored procedure. In this table, cart_id will be passed as stored procedure argument while other two columns are populated from another table. I want to insert latest 10 rows from other table and for all these rows, the cart_id will be same

First I tried,

BEGIN
insert into my_table (cart_id, product, sold_quantity) VALUES (cart_id, (Select product, sold_quantity from other_table limit 10))
END

Here, cart_id is passed in procedure argument. This obviously does not work as the coulmn count does not match. So, I tried,

BEGIN
insert into my_table (cart_id, product, sold_quantity) VALUES (cart_id, (Select product from other_table limit 10), (Select  sold_quantity from other_table limit 10))
END

This returns error Subquery returns more than 1 row

Is there any way I can populate my table with data from a combination of argument parameter and other table?

Hello @Charlieface, If you read my question properly, you will know the provided link is completely different than the question I have posted. Thanks. — Jaimin Sutariya, Mar 16 '23 at 07:15
Sorry I really don't understand why. You want to know how to insert multiple rows and columns from one table to another (with the slight tweak of an extra fixed column). This is done using INSERT ... SELECT ... which is what that answer shows. — Charlieface, Mar 16 '23 at 10:22
Yes @Charlieface, that small tweak was the reason I had to add a new question because I had not idea how to do it. No worries, I got the solution from Ergest's answer. — Jaimin Sutariya, Mar 16 '23 at 10:58

score 1 · Accepted Answer · answered Mar 15 '23 at 09:50

The problem is the VALUES in your particular case. Use INSERT INTO SELECT.

Query.

insert into my_table (cart_id, 
                      product, 
                      sold_quantity
                      ) 
select  cart_id, 
        product,
        sold_quantity
from other_table limit 10 ;

Example Consider the following data

create table other_table(
    product int ,
    sold_quantity int );
insert into other_table values  (1,10), (2,10), (3,10), (4,10), (5,10), 
(1,10), (2,10), (3,10), (4,10), (5,10);
create table my_table(
    cart_id int,
    product int(9) ,
    sold_quantity int );

Procedure

CREATE PROCEDURE insert_into_table(cart_id int )
BEGIN       
   insert into my_table (cart_id, 
                      product, 
                      sold_quantity
                      ) 
   select  cart_id, 
           product,
           sold_quantity
   from other_table limit 10 ;
END;

Procedure call

call insert_into_table(5);

Result

select * from my_table;
cart_id product sold_quantity
   5       1    10
   5       2    10
   5       3    10
   5       4    10
   5       5    10
   5       1    10
   5       2    10
   5       3    10
   5       4    10
   5       5    10

https://dbfiddle.uk/PWCgNy0j

Stored procedure to Insert data into table with select statement and argument parameter

1 Answers1