Inserting multiple rows in mysql

349

votes

Is the database query faster if I insert multiple rows at once:

like

INSERT....

UNION

INSERT....

UNION

(I need to insert like 2-3000 rows)

mysqlsql-insert

UNION is for selects. – Jacob

How the rows are coming? Are you directly writing SQL on MySQL Query Browser or using from inside PHP or C# or something else. If you fit in the latter case, then check following links: The Fastest Way to Insert 100k Records Fast Inserts with Multiple Rows – RKh

Fixed second link: Optimize MySQL Queries — Fast Inserts With Multiple Rows – Pavel Shkleinik

1273

votes

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

63

votes

If you have your data in a text-file, you can use LOAD DATA INFILE.

When loading a table from a text file, use LOAD DATA INFILE. This is usually 20 times faster than using INSERT statements.

Optimizing INSERT Statements

You can find more tips on how to speed up your insert statements on the link above.

31

votes

BEGIN;
INSERT INTO test_b (price_sum)
  SELECT price
  FROM   test_a;
INSERT INTO test_c (price_summ) 
  SELECT price
FROM   test_a;
COMMIT;

-1

votes

Here is a PHP solution ready for use with a n:m (many-to-many relationship) table :

// get data
$table_1 = get_table_1_rows();
$table_2_fk_id = 123;

// prepare first part of the query (before values)
$query = "INSERT INTO `table` (
   `table_1_fk_id`,
   `table_2_fk_id`,
   `insert_date`
) VALUES ";

//loop the table 1 to get all foreign keys and put it in array
foreach($table_1 as $row) {
    $query_values[] = "(".$row["table_1_pk_id"].", $table_2_fk_id, NOW())";
}

// Implode the query values array with a coma and execute the query.
$db->query($query . implode(',',$query_values));

-13

votes

// db table name / blog_post / menu /  site_title
// Insert into Table (column names separated with comma)
$sql = "INSERT INTO product_cate (site_title, sub_title) 
  VALUES ('$site_title', '$sub_title')";

// db table name / blog_post / menu /  site_title
// Insert into Table (column names separated with comma)
$sql = "INSERT INTO menu (menu_title, sub_menu)
  VALUES ('$menu_title', '$sub_menu', )";

// db table name / blog_post /  menu /  site_title
// Insert into Table (column names separated with comma)
$sql = "INSERT INTO blog_post (post_title, post_des, post_img)
  VALUES ('$post_title ', '$post_des', '$post_img')";

Inserting multiple rows in mysql

5 Answers