CODEWITHSUNDEEP
mysqlcopy
blarg
blarg

Reputation: 3893

Copy values from one table to another only where columns exist in both?

I'm trying to copy a table from one database to another like so:

INSERT INTO db1.content SELECT * FROM db2.content;

Some fields have been added and some have had the names changed. So I would like to set a condition where only the columns that exist in both tables are copied across.

Maybe something like this, where * is a wildcard for column name.

INSERT INTO db1.content SELECT * FROM db2.content WHERE db1.* = db2.*;

Upvotes: 2

Views: 269

Answers (1)

Karunakar
Karunakar

Reputation: 2349

You can do this by following procedure where table1 is the source table and table2 is the target table:

CREATE DEFINER=`root`@`localhost` PROCEDURE `copySimilarTableContent`(in table1 varchar(50), in table2 varchar(50))
    BEGIN
     drop temporary table if exists tempTable1;
     create temporary table tempTable1
      select column_name from information_schema.columns where `TABLE_SCHEMA`='db2' and table_name=table1;

     drop temporary table if exists tempTable2;
     create temporary table tempTable2
      select column_name from information_schema.columns where `TABLE_SCHEMA`='db1' and table_name=table2;

     set @common_col = (select group_concat(t1.column_name)
          from tempTable1 t1
          left join tempTable2 t2 on t1.column_name = t2.column_name
          where t2.column_name is not null);

     set @sql = concat("insert into ", table2, " (",@common_col,") select ", @common_col, " from ", table1);

      PREPARE stmt FROM @sql;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;


    END

Upvotes: 1

Related Questions