14

I'm wondering how you go about doing a bulk database INSERT in Yii2?

For example a normal INSERT I would like so:

$sql = $this->db("INSERT INTO some_table(id,my_value) VALUES(:id,:my_value)");
$sql->bindValues([':id' => $id, ':my_value' => $my_value]);
$sql->execute();

Now what if I wanted to create a bulk INSERT?

Without binding values you could it something like:

foreach ($foo as $bar) {
    $data_sql .= '(' . $id . ',' "'" . $bar . "'),"
}

$data_sql = rtrim($data_sql, ',');

$sql = $this->db("INSERT INTO some_table(id,my_value) VALUES" . $data_sql);
$sql->execute();

But how can you achieve this if you still want to bind the values?

Edit: This question is not the same as the linked one as I am not using ActiveRecord.

Edit 2:

Ideally it would be good if there was a solution that offered some flexibility, such as being able to write most of your own syntax, as one of the answers posted below:

Yii::$app->db->createCommand()->batchInsert('tableName', ['id', 'title', 'created_at'], [
    [1, 'title1', '2015-04-10'],
    [2, 'title2', '2015-04-11'],
    [3, 'title3', '2015-04-12'],
])->execute();

...doesn't offer that. For this particular situation I need to use the IGNORE statement, which the above solution doesn't offer.

Brett
  • 19,449
  • 54
  • 157
  • 290
  • Create something like `INSERT INTO some_table(id,my_value) VALUES (?, ?),(?, ?),(?, ?),(?, ?)` and fill placeholders? – u_mulder Apr 11 '15 at 17:41
  • Well I had thought of doing something similar by appending numbers to the end of each placeholder, but was hoping there was a better way. – Brett Apr 11 '15 at 17:50
  • possible duplicate of [ActiveRecord batch insert (yii2)](http://stackoverflow.com/questions/27355262/activerecord-batch-insert-yii2) – arogachev Apr 12 '15 at 05:21

2 Answers2

33

There is a special batchInsert method for that:

Yii::$app->db->createCommand()->batchInsert('tableName', ['id', 'title', 'created_at'], [
    [1, 'title1', '2015-04-10'],
    [2, 'title2', '2015-04-11'],
    [3, 'title3', '2015-04-12'],
])->execute();
Pavel Bariev
  • 2,546
  • 1
  • 18
  • 21
  • I assume this will not do any validation – Developerium Apr 12 '15 at 03:48
  • I assume this has the same security as when you bind values where you don't need to sanitize the data before passing it in? Also, the unfortunate problem with this is that it's not very flexible, such as if you want to use other stuff in the query such as `INSERT IGNORE`. – Brett Apr 12 '15 at 07:12
  • This method is secure as it uses special methods for values quoting. And if you need you may extract its final sql query and change it. – Pavel Bariev Apr 12 '15 at 10:19
  • I assume though if you extract the query then to use something such as `IGNORE` in the statement you would need to do an `str_replace` such as `str_replace('INSERT', 'INSERT IGNORE', $query)` ? – Brett Apr 12 '15 at 13:04
  • Yes, I would try this. – Pavel Bariev Apr 12 '15 at 17:08
  • There was a similar issue https://github.com/yiisoft/yii2/issues/5138 but it's still under discussion because of database differences. So I'm pretty sure you'll need a kind of workaround here. – Pavel Bariev Apr 12 '15 at 17:23
  • Thanks for the link. I ended up just doing a straight SQL statement without binding and sanitizing the values with `quoteValues()`. – Brett Apr 12 '15 at 17:38
0

This code bind values as placeholders without ActiveRecord

\Yii::$app->db->createCommand('INSERT INTO some_table (id, my_value) VALUES(:id, :value')
    ->bindValue(':id', $id)
    ->bindValue(':value', $value)
    ->execute();
atrichkov
  • 450
  • 3
  • 6
  • This concept is actually good if you want to insert values from other table and include some validations. Example: ``` \Yii::$app->db->createCommand(' INSERT INTO table (id, value) SELECT id, value FROM other_table WHERE condition > :something ', [ ':something' => $value ])->execute(); ``` – Uriel Nov 11 '21 at 22:16