CODEWITHSUNDEEP
mysqlsql-insert
Emma
Emma

Reputation: 3539

Inserting multiple rows in mysql

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)

Upvotes: 349

Views: 1043608

Answers (5)

Meloman
Meloman

Reputation: 3722

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

EDIT : After @john's comment I decided to enhance this answer with a more efficient solution :

  • divides the query to multiple smaller queries
  • use rtrim() to delete last coma instead of implod()
// limit of query size (lines inserted per query)
$query_values  = "";
$limit         = 100;
$table_1       = get_table_1_rows();
$table_2_fk_id = 123;

$query = "INSERT INTO `table` (
   `table_1_fk_id`,
   `table_2_fk_id`,
   `insert_date`
) VALUES ";

foreach($table_1 as $row) {
    $query_values .= "(".$row["table_1_pk_id"].", $table_2_fk_id, NOW()),";
    
    // entire table parsed or lines limit reached :
    // -> execute and purge query_values
    if($i === array_key_last($table_1) 
    || fmod(++$i / $limit) == 0) {
        $db->query($query . rtrim($query_values, ','));
        $query_values = "";
    }
}

Upvotes: -2

sunilsingh
sunilsingh

Reputation: 499

Just use a SELECT statement to get the values for many lines of the chosen columns and put these values into columns of another table in one go. As an example, columns "size" and "price" of the two tables "test_b" and "test_c" get filled with the columns "size" and "price" of table "test_a".

BEGIN;
INSERT INTO test_b (size, price)
  SELECT size, price
  FROM   test_a;
INSERT INTO test_c (size, price) 
  SELECT size, price
  FROM   test_a;
COMMIT;

The code is embedded in BEGIN and COMMIT to run it only when both statements have worked, else the whole run up to that point gets withdrawn.

Upvotes: 36

Johirulla
Johirulla

Reputation: 1

// 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')";

Upvotes: -16

Jacob
Jacob

Reputation: 43319

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.

Upvotes: 64

Nicola Cossu
Nicola Cossu

Reputation: 56407

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

Upvotes: 1492

Related Questions