Reputation: 3539
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
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 :
// 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
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
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
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.
You can find more tips on how to speed up your insert statements on the link above.
Upvotes: 64
Reputation: 56407
INSERT
statements that useVALUES
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);
Upvotes: 1492