MySQL merge two tables of different database to single database Friends, i have database named as DB1 with a table called TB1A and another database as DB2 with  a table TB2A.
TB1A having 5 columns and TB2A  having 8 columns, All the columns available in TB1A is present in TB2A.
I want to create new table called TB3A which should be a merged result of a TB1A and TB2A. Means TB3A will have 8 columns and contains all the datas of TB1A and TB2A.
CREATE TABLE TB3A AS (

SELECT * 
  FROM DB1.TB1A 
  JOIN  DB2.TB2A 
) 

While i trying like above it is showing an error like below

Error Code: 1060
Duplicate column name 'Agreement_Id'
Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0 sec

Does any one have any idea about this. I have google it this issue but i am not able to find the solution for this ..
What you've entered is equivalent to:

CREATE TABLE TB3A AS (

SELECT a.AgreementID, a.Column2, a.Column3, a.Column4,
       a.Column5, b.AgreementID, b.Column2, b.Column3,
       b.Column4, b.Column5, b.Column6, b.Column7, b.Column8
  FROM TB1A a JOIN TB2A b
);


You'd have 13 columns, with only 8 distinct names. This is a problem.

What you probably want to do is this:

CREATE TABLE TB3A AS (

SELECT AgreementID, Column2, Column3, Column4,
       Column5, Column6, Column7, Column8
  FROM TB2A
);

INSERT INTO TB3A (AgreementID, Column2, Column3, Column4, Column5)
SELECT * FROM TB1A;


TB3A will now have m + n rows, where m is the number of rows in TB1A, and n is the number of rows in TB2A.

If some rows in TB1A and TB2A are duplicates, this will not remove those. You can change the INSERT ... SELECT statement to filter out rows from TB1A that match rows already in TB3A (thus, originally from TB2A) with matching values in whatever columns you need. For instance, to not bring in rows from TB1A that have the same AgreementId as rows already in TB3A, the INSERT would be:

INSERT INTO TB3A (AgreementID, Column2, Column3, Column4, Column5)
SELECT * FROM TB1A 
 WHERE NOT EXISTS (SELECT 1 FROM TB3A
                    WHERE TB1A.AgreementID = TB3A.AgreementId
                  )
;




Additional note on the original statement: JOIN is generally assumed to be an INNER JOIN - one that matches rows form the two tables based on one or more criteria. For example, TB1A JOIN TB2A ON TB1A.AgreementId = TB2A.AgreementId would return the rows from TB1A and TB2A the had the same AgreementID. Rows in TB1A with an AgreementID not found in TB2A would not be returned, and rows in TB2A with an AgreementID not found in TB1A would not be returned.

If there were three rows in TB1A with the same AgreementId, and 2 rows in TB2A with that same ID, those rows would generate 6 rows in the result set:

TB1A      TB2A
  x         m
  y         n
  z

Result set:
    x,m
    x,n
    y,m
    y,n
    z,m
    z,n


In some SQL dialects (MySQL included), if there is no JOIN criteria specified, then the INNER JOIN becomes what's called a CROSS JOIN. Here, since there's nothing to indicate which rows should not be joined together, all rows are joined together. If you have m rows in TB1A, and n rows in TB2A, you don't get m+n rows - you get m*n rows. That's what would have happened, if your original statement had run.