CODEWITHSUNDEEP
mysqlsqlmerge
Sufendy
Sufendy

Reputation: 1242

Merging 2 tables with ids that overlap

I have 2 tables, their structure is identical but used to serve in 2 different applications. So, each of them have unique records (records from app1 won't exist in app2). But their Ids are maintained separately by the 2 apps. So, ids from app1 might exist in app2 but with different information.

I need to merge these 2 tables records into one table because the 2 applications are being merged into one. The problem is with the ids. I will probably reset the ids and regenerate the unique id for each record. The ids is not referenced by other tables, so resetting it is not a problem. But I'm not really sure how to execute this.

Upvotes: 1

Views: 1698

Answers (3)

Stephane Gosselin
Stephane Gosselin

Reputation: 9148

So, ids from app1 might exist in app2 but with different information.

One option you may want to look into is using a view to consolidate the data of both tables in a format acceptable for whatever application.

Upvotes: 0

Tudor Constantin
Tudor Constantin

Reputation: 26861

The INSERT INTO .. SELECT FROM is to be used here - without the id column. Aslo, I would put an application_id column into the new table - just to be sure that I know what data came from where:

INSERT INTO your_table(app_id, col1,co2,.....) 
SELECT 1, col1,col2,... 
FROM old_table1

For application 2:

INSERT INTO your_table(app_id, col1,co2,.....) 
SELECT 2, col1,col2,... 
FROM old_table2

Upvotes: 1

niktrs
niktrs

Reputation: 10066

Create a table with an auto incremend (id) column.

Then create an INSERT ... SELECT query to import data from each table

Eg 

INSERT INTO new_table(column1,column2,.....) -- ommiting the id column
SELECT column1,column2,... -- ommiting the id column
FROM old_table1

INSERT INTO new_table(column1,column2,.....) -- ommiting the id column
SELECT column1,column2,...-- ommiting the id column
FROM old_table2

Upvotes: 6

Related Questions