Bulk insert or update yii2

Question

What I did

I am inserting bulk data in table as follows:

if(count($bulkInsertArray)>0){
    $columnNameArray=['columnName1','columnName2','columnName3'];
    // below line insert all your record and return number of rows inserted
    $insertCount = Yii::$app->db->createCommand()
                   ->batchInsert(
                         $tableName, $columnNameArray, $bulkInsertArray
                     )
                   ->execute();
}

REF: Insert multiple data into database in Yii 2

and here insert working fine.

What I want:

Now My question is can we do something like if here columnName1 is private key and if we are passing null value for that column then perform insert operation otherwise perform update operation.

Same concept working in CAKEPHP.

I am using YII2.

score 0 · Answer 1 · answered Apr 08 '15 at 13:42

Yii2 doesn't provide "upsert" logic.
You need split it in two operations:
1) insert without duplicates (queries bellow)
2) update

--don't insert user if login already exists
insert into users(login, name)
select *
from 
(
    select
    'l_john' as login,
    'John' as name
    union 
    select
    'l_mike' as login,
    'Mike' as name
) q
left join users u
on u.login = q.login
where u.id is null

or

create temp table tmp_users(login, name);

insert into tmp_users(login, name) values
('l_john', 'John'),
('l_mike', 'Mike');

insert into users(login, name)
select tu.login, tu.name
from tmp_users ti
left join users u
on u.login = tu.login

second query set is more fastest

score 0 · Answer 2 · answered Apr 14 '15 at 10:36

I have tried so lot but at the end completed it with loop like:

            foreach ($table_info_arr as $table_info)
            {
                $table_info_sql = array();

                $model = tableInfo::find()->where(["columnName1_id" => $table_info['columnName1_id']])->one();
                if (empty($model))
                {
                    $model = new tableInfo();
                }
                $matches_info_sql['tableInfo'] = $table_info;

                $model->load($table_info_sql);
                $model->save(false);
            }

Let me know if is there any better solution

Bulk insert or update yii2

2 Answers2