How to duplicate a record

Yii 2.0 General Discussions
1

I’m needing to duplicate a record and can’t seem to get it to work.

In MySQL, my code worked fine, but now that I have to migrate to SQL Server it errs.

Originally, my code was

$project = $this->findModel($id);
$model = new Projects(
	$project->getAttributes() // get all attributes and copy them to the new instance
);
$model->ProjId = null;
$model->ProjNo = Projects::find()->max('ProjNo') + 1;
$model->StatusId = 4;
$model->StartDt = date('Y-m-d H:i:s');
$model->save(false);

Since migrating to SQL Server I am receiving the error

Cannot insert explicit value for identity column in table ‘TableName’ when IDENTITY_INSERT is set to OFF

I started Googling and tried modifying my code to use clone

$project = $this->findModel($id);
$model = clone $project;
$model->isNewRecord = true;
$model->ProjNo = Projects::find()->max('ProjNo') + 1;
$model->StatusId = 4; //Opened
$model->StartDt = date('Y-m-d H:i:s');
$model->save(false);

but this hasn’t helped.

What is the proper way to duplicate a record?

2

For anyone else needing to do this I’ve found 2 approaches that work.

  1. Creating a new model and populating each attribute 1 by 1

    $project = $this->findModel($id);
    $model = new Projects();
    //Special attributes that need to be overridden
    $model->ProjNo = Projects::find()->max(‘ProjNo’) + 1;
    $model->StatusId = 4;
    $model->StartDt = date(‘Y-m-d H:i:s’);
    //Normal attribute that I want to copy/duplicate from the original
    $model->EndDt = $project->EndDt;
    $model->From = $project->From;
    $model->To = $project->To;
    // … Do this for every single attribute in the table!
    $model->save(false);

This way the PK is never defined. A bit tedious, because you need to define every single attribute value but then again gives you full control.

  1. Using clone

    $model2 = clone $project;
    $model2->isNewRecord = true;
    unset($model2[‘PKAttributeName’]);
    //Special attributes that need to be overridden
    $model->ProjNo = Projects::find()->max(‘ProjNo’) + 1;
    $model->StatusId = 4;
    $model->StartDt = date(‘Y-m-d H:i:s’);
    $model->save(false);

by unsetting (removing it from the array submitted) the PK field, the issue is solved. For larger tables, cloning requires less code because you don’t need to copy each attribute 1 by 1 like in approach 1 and you can unset as many attributes as required to reset them in the new/duplicated record.

1 Like
3

Don’t use the “clone approach”.
Yii2 has a nice feature called “massive assignment” which uses model’s ‘setAttributes’ and ‘getAttributes’ methods.
IMO the proper Yii way is (not tested, but should work):

$project = $this->findModel($id);
$model = new Projects(); 
// massive assignment
$model->attributes = $project->attributes;
// unset PK
$model->PKAttributeName = null; // or unset()
// new values
$model->ProjNo = Projects::find()->max(‘ProjNo’) + 1;
$model->StatusId = 4;
$model->StartDt = date(‘Y-m-d H:i:s’);
$model->save(false);
4

Thank you for the reply, I will give it a try. Nullifying the attribute (PK) didn’t work with SQL Server (my original post), but I never tried unsetting it, and will.

For my personal learning, is there something specifically wrong with the clone approach, a reason it should be avoided?

Thank you once again. Always good to learn new things.

5

Because the Yii model, especially the active record, (in your case) is a rich object containing lot of other attributes and methods such as scenarios, validators, old values, etc.
FYI: massive assignment
Yii2 has great documentation, please read it !

6

Just to confirm for others that the massaive assignment (which was my original code) does work, but setting the PK attribute to null does not (well not for me after switching to Azure), instead you have to use unset().

So retaking my original code, the solution becomes.

$project = $this->findModel($id);
$model = new Projects(
	$project->getAttributes() // get all attributes and copy them to the new instance
);
unset($model['ProjId']);  // $model->ProjId = null; does not work, must use unset instead!
$model->ProjNo = Projects::find()->max('ProjNo') + 1;
$model->StatusId = 4;
$model->StartDt = date('Y-m-d H:i:s');
$model->save(false);

Thank you rdavidcz for your help!

7

Just as a side remark, in the Relational Model - foundation of all SQL database systems, including MySQL - a record is always unique, so by definition can not be duplicated.
Uniqueness is enforced by the primary key (PK) being defined with the UNIQUE constraint in the database definition.
Your error message “Cannot insert … when IDENTITY_INSERT is set to OFF” seems to indicate that you tried to INSERT the full record including the (same old) PK - while, if you set that parameter ON, the DBMS would auto-generate a new PK.
What you can do, and it seems this is what you wanted:
Copy the content of a record - all fields except the PK - to a new one and add a new, unique PK (or have the DBMS do this automatically, see above).
Then again, neither the DBMS nor a user can learn about this action, i.e. there is no such status as ‘copied from’ or ‘links to’, similar to a symbolic link (Unix) or shortcut (Windows). If you wanted that, you could design an extra table (n:m) which links ‘copy’ to ‘original’ records, be them in the same table.

8

BTW, probably this method can be useful in this kind of case: https://www.yiiframework.com/doc/api/2.0/yii-db-baseactiverecord#setIsNewRecord()-detail