Yii2 Batch Insert

Learn how to insert bulk data with only single query in Yii2 using Batch Insert

We are going to do a batch insert in this tutorial, so what is the batch insert? Well a batch insert is a way that you can enter multiple numbers of records at once to the database.

Suppose we have an excel sheet that has 10,000 rows if we want to import those 10,000 rows in the database using this example.

foreach($row as $key=>$value){ $model = new model(); $model->column1 = $value->column1; $model->column2 = $value->column2; $model->column3 = $value->column3; $model->column4 = $value->column4; $model->column5 = $value->column5; $model->save(); } 1 2 3 4 5 6 7 8 9 foreach ( $row as $key = > $value ) { $model = new model ( ) ; $model -> column1 = $value -> column1 ; $model -> column2 = $value -> column2 ; $model -> column3 = $value -> column3 ; $model -> column4 = $value -> column4 ; $model -> column5 = $value -> column5 ; $model -> save ( ) ; }

We will be creating 10,000 model objects and we will be sending 10,000 queries to the database, this is very bad you might know that there is a way that we can have insert multiple number of records to a database with only one query.

Yii2 provides a special function that allow us to do this and makes it really easy so let’s take a look at this function, it’s called the batchInsert()

Yii::$app->db ->createCommand() ->batchInsert('table_name', ['column1','column2', 'column3','column4','column5'],$data) ->execute(); 1 2 3 4 Yii:: $app -> db -> createCommand ( ) -> batchInsert ( 'table_name' , [ 'column1' , 'column2' , 'column3' , 'column4' , 'column5' ] , $data ) -> execute ( ) ;

This batchInsert() function takes three parameters; the first parameter is the table name, next argument is the number of columns, finally in the third argument we have to add the array of records.

Now let’s create the array of records "$data" in our previous loop.

$data = array(); foreach($row as $key=>$value){ $data[] = [$value->column1,$value->column2, $value->column3, $value->column4, $value->column5]; } Yii::$app->db ->createCommand() ->batchInsert('table_name', ['column1','column2', 'column3','column4','column5'],$data) ->execute(); 1 2 3 4 5 6 7 8 $data = array ( ) ; foreach ( $row as $key = > $value ) { $data [ ] = [ $value -> column1 , $value -> column2 , $value -> column3 , $value -> column4 , $value -> column5 ] ; } Yii:: $app -> db -> createCommand ( ) -> batchInsert ( 'table_name' , [ 'column1' , 'column2' , 'column3' , 'column4' , 'column5' ] , $data ) -> execute ( ) ;

$data array will look like this: