How to write Insert Query in Yii2?

Question

In Yii2 How to insert data from one table to another table.

Here i have two tables table1 and table2.

Now what i need is when a condition met i need transfer a specific data from table1 to table2.

so help to write insert Query for this scenario in Yii2

This is the insert query given in yii2 docs

Yii::$app->db->createCommand()
->insert('user', [
'name' => 'Sam',
'age' => 30,
])->execute();

But i need this Query to be convert according Yii2 Query

INSERT INTO Customers (CustomerName, Country)
SELECT SupplierName, Country FROM Suppliers;

arogachev · Answer 1 · 2015-05-06T06:38:00.483

4

QueryBuilder's insert method returns this:

return 'INSERT INTO ' . $schema->quoteTableName($table)
    . ' (' . implode(', ', $names) . ') VALUES ('
    . implode(', ', $placeholders) . ')';

So there is no way to specify SELECT here.

Can't find it in core, I thinks it's not implemented because it's pretty rare case.

You can use your custom SQL code like this:

$sql = '...';

\Yii::$app->db->createCommand($sql)->execute();

Useful links:

P.S. I also reported issue here, so maybe it will be added to the core in the future. If you want it now for repeated usage, you can implement such method by yourself.

edited May 06 '15 at 06:38

answered May 06 '15 at 06:18

arogachev

33,150
7
114
117

I can't understand help me to write for these two tables **table1** name,status,problem,text **table2** name,status,problem,text – Kalai S May 06 '15 at 06:30
What exactly you can't understand? – arogachev May 06 '15 at 06:31
Mentioned query for converting doesn't contain `table1`. Correct SQL according to your needs and use the method I described in answer. – arogachev May 06 '15 at 06:33
where i can give table1 name, table2 name and columns name belongs to each – Kalai S May 06 '15 at 06:33
1

As I said, correct SQL query. Question doesn't contain any information about `table1` and `table2` except their names. And your example is not even related with these tables. – arogachev May 06 '15 at 06:37
sorry for saying this again really I can't understand help me to write for these two tables table1name: **Claim** columns: **name,status,problem,text**, table2name: **pending** columns: **name,status,problem,text** , here how can i insert data from claim table to pending table – Kalai S May 06 '15 at 06:58
How can i write Query for this – Kalai S May 06 '15 at 07:00
1

Thank you for this answer. – Amit Kadam Dec 01 '21 at 05:01

score 1 · Answer 2 · answered Jun 08 '22 at 11:35

In the latest versions of YII2 (> 2.0.11), it is possible to do this.

Create a sub query, and pass it to the insert method of the DB object.

This will generate an INSERT INTO ... SELECT

eg.

$query = (new Query())
    ->select([
        'CustomerName' => 'SupplierName',
        'Country' => 'Country'
    ])
    ->from('Suppliers');

Yii::$app->db->createCommand()
    ->insert('Customers', $query)
    ->execute();

How to write Insert Query in Yii2?

2 Answers2