merge two tables with different columns mysql

Question

I want to merge two tables with different columns mysql. For e.g.

Table 1 :

-------------------------------------------------------------
item_id   title   slug   type   views   updatedAt   createdAt
-------------------------------------------------------------
 1    sometitle someslg  1     43454    timestamp   timestamp
 2    sometitle someslg  1     43566    timestamp   timestamp

Table 2:

-------------------------------------------------------------
id    ptitle   slug     pviews   updatedAt   createdAt
-------------------------------------------------------------
 1  sometitle  someslg  3434    timestamp   timestamp
 2  sometitle  someslg  6454    timestamp   timestamp
 3  sometitle  someslg  5454    timestamp   timestamp

The above tables are examples. I have merged with UNION. Here is my query

SELECT * ((SELECT t1.item_id,t1.title,t1.slug,t1.type,t1.views,t1.updatedAt,t1.createdAt 
FROM table1
t1) UNION ALL (SELECT t2.id,t2.ptitle,t2.slug,'',t2.pviews,t2.updatedAt,t2.createdAt)) t3 
ORDER BY t3.item_id ASC

This is working fine After Executing the query i will get the results like

-------------------------------------------------------------
item_id   title   slug   type   views   updatedAt   createdAt
-------------------------------------------------------------
 1    sometitle someslg  1     43454    timestamp   timestamp
 1    sometitle someslg        3434     timestamp   timestamp
 2    sometitle someslg  1     43566    timestamp   timestamp
 2    sometitle someslg        6454     timestamp   timestamp
 3    sometitle someslg        5454     timestamp   timestamp

But i want a virtual column in the result that shows the row from which table as shown below

--------------------------------------------------------------------------
item_id   title   slug   type   views   updatedAt   createdAt   from_tbl
--------------------------------------------------------------------------
 1    sometitle someslg  1     43454    timestamp   timestamp   t1
 1    sometitle someslg        3434     timestamp   timestamp   t2
 2    sometitle someslg  1     43566    timestamp   timestamp   t1
 2    sometitle someslg        6454     timestamp   timestamp   t2
 3    sometitle someslg        5454     timestamp   timestamp   t2

Answer 1

Just add the column in:

SELECT t3.*
FROM ((SELECT t1.item_id, t1.title, t1.slug, t1.type, t1.views, t1.updatedAt, t1.createdAt, 't1' as from_tbl
       FROM table1 t1
      ) UNION ALL
      (SELECT t2.id, t2.ptitle, t2.slug, '', t2.pviews, t2.updatedAt, t2.createdAt, 't2'
      )
     ) t3
ORDER BY t3.item_id ASC

Answer 2

try this way

SELECT t3.*
FROM ((SELECT t1.item_id, t1.title, t1.slug, t1.type, t1.views, t1.updatedAt, t1.createdAt, '1' as from_tbl
       FROM table1 t1
      ) UNION ALL
      (SELECT t2.id, t2.ptitle, t2.slug, '', t2.pviews, t2.updatedAt, t2.createdAt, '2'  FROM table2 t2
      )
     ) t3
ORDER BY t3.item_id ASC

Answer 3

CREATE TABLE table3 AS ( select t1.*,t2.* from table1 as t1, table2 as t2 where t1.LinkID1 = t2.LinkID2 ); INSERT INTO table3

** LinkID1 and LinkID2 are index referance each together

Answer 4

This sql script can achieve the same purpose without carrying out the headache of specifying the whole list of column names. This script can guess the match column names and remove duplicates on insertion process.

SET @target_table = '`klame_new`.`url`';
SET @source_table = '`klame_url`.`url`';

SELECT GROUP_CONCAT(column_name)
 FROM INFORMATION_SCHEMA.COLUMNS a 
 WHERE a.TABLE_NAME = 'url'
 AND a.TABLE_SCHEMA = 'klame_url'
 AND a.COLUMN_NAME IN (
   SELECT b.COLUMN_NAME
   FROM INFORMATION_SCHEMA.COLUMNS b 
   WHERE b.TABLE_NAME = 'url'
   AND b.TABLE_SCHEMA = 'klame_new' 
 ) INTO @columns;

SET @s = CONCAT('REPLACE INTO ', @target_table , ' (', @columns ,') ' , ' SELECT ', @columns ,' FROM ', @target_table ,' UNION ALL SELECT ', @columns , ' FROM ', @source_table);

PREPARE stmt FROM @s;
EXECUTE stmt;