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 · Accepted Answer · 2015-05-06 06:38:00Z

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 6, 2015 at 6:38

answered May 6, 2015 at 6:18

arogachev

33.6k8 gold badges119 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Kalaiselvan Mahendiran Over a year ago

I can't understand help me to write for these two tables table1 name,status,problem,text table2 name,status,problem,text

arogachev Over a year ago

What exactly you can't understand?

arogachev Over a year ago

Mentioned query for converting doesn't contain table1. Correct SQL according to your needs and use the method I described in answer.

Kalaiselvan Mahendiran Over a year ago

where i can give table1 name, table2 name and columns name belongs to each

arogachev Over a year ago

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.

|

Richard Cross · Accepted Answer · 2022-06-08 11:35:07Z

3

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

answered Jun 8, 2022 at 11:35

Richard Cross

4745 silver badges14 bronze badges

Collectives™ on Stack Overflow

How to write Insert Query in Yii2?

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related