CODEWITHSUNDEEP
mysqlimport
DBC
DBC

Reputation: 72

Importing from old table into new table with different structure

My old table had around 600 columns. Let's call this table old_table.

My new table has 603 columns. Let's call this table new_table.

Now to import the data from old_table into new_table, I tried following the below approach:

$q = mysql_query("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
              WHERE TABLE_SCHEMA = 'databasename' AND TABLE_NAME = 'old_table'");

$cols = array();
while($r = mysql_fetch_array($q))
{   
   $cols[] = $r['COLUMN_NAME'];     
}

$sql = "INSERT INTO new_table ";
$sql .= "SELECT ";
$sql .= implode(", ", $cols);
$sql .= " FROM old_table";

mysql_query($sql) or die("Something went wrong: ".mysql_error());

But this is resulting into Column count doesn't match value count at row 1 because the query is also taking into account the 3 new columns of new_table.

Is there a way I can make it skip the additional columns in the new_table and insert all data from old_table?

Upvotes: 1

Views: 159

Answers (1)

SQB
SQB

Reputation: 4078

Yes. You can either provide default values for the new columns and add those to your insert statement, or you can name the columns you want to insert into.

$sql = "INSERT INTO new_table ";
$sql .= "SELECT ";
$sql .= implode(", ", $cols);
$sql .= ", value_for_new_column_1";
$sql .= ", value_for_new_column_2";
$sql .= ", value_for_new_column_3";
$sql .= " FROM old_table";

$sql = "INSERT INTO new_table ";
$sql .= "(" . implode(", ", $cols) . ")";
$sql .= "SELECT ";
$sql .= implode(", ", $cols);
$sql .= " FROM old_table";

Upvotes: 1

Related Questions