Insert Multiple Rows With One Query MySQL

Inserting multiple rows in mysql

INSERT statements that use VALUES syntax can insert multiple rows. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas.

Example:

INSERT INTO tbl_name
    (a,b,c)
VALUES
    (1,2,3),
    (4,5,6),
    (7,8,9);

Source

MySQL insert multiple rows with one select

Following will be the fastest way. elimincates multiple selects.

 SELECT ID, Price_Vendor_A, Stock_Vendor_A,Price_Vendor_B, Stock_Vendor_B into @ID, @Price_Vendor_A,@Stock_Vendor_A,@Price_Vendor_B,@Stock_Vendor_B FROM temp_table2 LIMIT 1;

insert ignore into temp_table (ID, Price, Stock, Vendor) SELECT @ID as ID, @Price_Vendor_A as price, @Stock_Vendor_A as stock ,"Vendor_A" AS Vendor UNION ALL  SELECT @ID as ID, @Price_Vendor_B as price, @Stock_Vendor_B as stock ,"Vendor_B" AS Vendor

MySQL insert multiple rows with select and one constant value

VALUES() lets you insert just one row, while you want to insert multiple rows (one per row in the payment_methods_default table). For this, consider the insert ... select syntax:

insert into payment_methods_assigned_to_users (user_id, name) 
select 1, name from payment_methods_default

How can I insert multiple rows in the same table by one query?

Use:

INSERT INTO mytable (id, name) VALUES 
(NULL, :name1),
(NULL, :name2)

Insert multiple rows from select result

Use INSERT...SELECT:

INSERT INTO permissionaction (AccessLevel, PermissionId, ControllerActionId, Active, CreatedOn, ModifiedOn, CreatedById, ModifiedById, RoleId)
SELECT 1, 19, 1, 1, Now(), Now(), 1, 1, Id
FROM role
WHERE id != 99
AND Id NOT IN (SELECT RoleId FROM permissionaction WHERE PermissionId = 19);

With INSERT...SELECT you can add values to the table permissionaction for each returned row of the SELECT query.

Insert multiple rows into MySQL Table ( PHP problem )

You can use MYSQL prepared statement

    include(your connection code...)

    // prepare and bind
    $stmt = $conn->prepare("INSERT INTO yourtable(user_id, message, date, subject) VALUES (?, ?, ?, ?)");
    $stmt->bind_param("isss", $user_id, $message, $date, $subject);

    @foreach statement for your data
    
    // set parameters and execute
    $user_id= $data->id;
    $message= $data->message;
    $date= $data->date;
    $subject= $data->subject;
    $stmt->execute();
    
    @endforeach

this method can even secure your app from SQL injection

SQL Query to INSERT multiple rows with SELECT

If I understand your question correctly, you are wanting to do a query on table1 that returns multiple rows, and then insert those into table2 in a single loop. That's the INSERT INTO SELECT statement:

  INSERT INTO table2
     (name, email, phone)
     SELECT name, email, phone
     FROM table1;

It can be modified to grab specific results as well:

  INSERT INTO table2
     (name, email, phone)
     SELECT name, email, phone
     FROM table1
     WHERE name = 'target person';

More information can be found at http://dev.mysql.com/doc/refman/5.7/en/insert-select.html and http://www.w3schools.com/sql/sql_insert_into_select.asp.

EDIT:

Based on your comment, it sounds like you're trying to do this:
SQL split values to multiple rows.

I can't think of a situation where you'd actually want to do that, as you can access all of the data in your existing table as is, and it seems to be bad practice to split data in the way you're requesting. However, the solutions in the above thread should be applicable to what you're trying to do.

Ultimately, you may want to look at how you're actually retrieving the data. Modifying that code would be a better idea :)

---

Related Topics