ashu February 2016

Insert row from one table to another table, which has extra column?

I am working on migration of data from one db1 to another db2.

I have one scenario, where table Person is present in both databases(db1 and db2).

But the table in db2 has one extra column. Which is best effective way to write the query to migrate data from db1 Person to db2 Person.

I have written as below, is this is the best way to write it? Because if i have more columns and only one extra column, due to which i need to mention all the column names in loop statement.

$select = $dbh1->prepare("SELECT * FROM person");
$insert = $dbh2->prepare("INSERT INTO PERSON VALUES (?,?,?,?,?)");

$select->execute; 
while ( my($PR_ID,$NAME,$LASTNAME) = $select->fetchrow_array )
{
  $insert->execute($PR_ID,$NAME,$LASTNAME,'NULL','NULL');
}

Answers


Zafar Malik February 2016

If there is column mismatch in source and target table then use syntax as per below-

insert into table1(col1,col2,col3) select col1,col2,col3 from table2;


mscha February 2016

Zafar's answer is probably the best and quickest way to do this.

But if you do want to do this row by row, one general rule for database access in a script is: never access a column without explicitly naming it. So:

  • No SELECT *
  • No INSERT INTO table_name VALUES (...)

A corrected version of your script, guessing the column names:

my $select = $dbh1->prepare("SELECT pr_id, name, lastname FROM person");
my $insert = $dbh2->prepare("INSERT INTO PERSON(pr_id, name, last_name) VALUES (?,?,?)");

$select->execute; 
while ( my($PR_ID,$NAME,$LASTNAME) = $select->fetchrow_array )
{
  $insert->execute($PR_ID,$NAME,$LASTNAME);
}

(Note that you don't need to specify the extra columns in the new table, as long as they are nullable, and you want the value to be NULL. If you do want to insert a NULL yourself, don't use 'NULL', but use undef, like ikegami says.)

Post Status

Asked in February 2016
Viewed 3,379 times
Voted 9
Answered 2 times

Search




Leave an answer