Insert data from database to another database table

You are viewing revision #2 of this wiki article.
This is the latest version of this article.
You may want to see the changes made in this revision.

« previous (#1)

According to below wiki we could migrate database schema to another database

http://www.yiiframework.com/wiki/701/migration-the-database-schema-to-another-one/

A simple way but writing a lot of code is using three nested for

  • each table
  • each row
  • each column (we could remove the last nested for, using agile code)

If you create the above algorithm using static code you have to modify the code each time you modify the database schema (tables or columns)

combined with above code you almost never worry about that

So, how can we transfer entire tables data without worry about adding/deleted/modification of the table columns?

In class Myglobals of the wiki above, add the below static method

public static function migrateTableData(CDbConnection $connSource, $tblSource = null, CDbConnection $connDest, $tblDest) {
        $cmd = $connSource->schema->getCommandBuilder()->createFindCommand($tblSource, new CDbCriteria());
        $rows = $cmd->queryAll();

        if (!empty($rows))
            $cmdTo = $connDest->schema->getCommandBuilder()->createMultipleInsertCommand($tblSource, $rows)->execute();
 }

In the MigrateMsqlToSqlite method of the previous wiki you could add a parameter to identify whether you want to save and data

so,modify the MigrateMsqlToSqlite tp

public static function MigrateMsqlToSqlite($withdata=false) {
 ...
    $cmd->createTable($tbl->name, $cols); //after of this line add the below line
    if ($withdata)
        $this->migrateTableData($connection, $tbl, Yii::app()->dblite, Yii::app()->dblite->schema->getTable($tbl->name));
 ...
 }

Tiny code both of two wikis but powerfull enough! Now you can migrate the database with(or not) its data!