How to Insert values in mysql from other databases

Question

I'm trying to INSERT INTO a table using other database.

I have 3 databases called A_DB, B_DB and C_DB

In each database i have a table called prvn with these columns and values :

--------------------------------------------------
A_DB. prvn : 
id (PK, A_I)   |   dm   |   Name  |
1                 1001      David
2                 1001      Sam

--------------------------------------------
B_DB. prvn : 
id (PK, A_I)   |   dm   |   Name  |
1                 1002      Bird
2                 1002      Cat

Now i want to insert values in to C_DB from A_DB and B_DB like this

C_DB. prvn : 
id             |   dm   |   Name  |
10011              1001     David
10012              1001     Sam
10021              1002     Bird
10022              1002     Cat

I use this code but it doesn't work :

INSERT INTO C_DB.prvn (id, dm, Name) VALUES (
SELECT (dm + id), dm, Name FROM A_DB.prvn 
WHERE (A_DB.prvn.id + A_DB.prvn.dm) NOT IN (SELECT id FROM C_DB.prvn)
)

INSERT INTO C_DB.prvn (id, dm, Name) VALUES (
SELECT (dm + id), dm, Name FROM B_DB.prvn 
WHERE (B_DB.prvn.id + B_DB.prvn.dm) NOT IN (SELECT id FROM C_DB.prvn)
)

Please help how to fix it

Are you only going to insert into the new table this way? If so I see no reason why the id column must be an int and have auto_increment. — Joakim Danielson
– Joakim Danielson, Commented May 27, 2018 at 10:41

Joakim Danielson · Accepted Answer · 2018-05-27 11:22:33Z

2

Instead of dm + id do dm * 10 + id to get unique id values in the new table.

Not sure why you have the WHERE clause, are you doing this multiple times? If so you of course need to multiply by 10 in the WHERE clause as well.

Update

There is a syntax issue with your INSERT INTO, it should look like

INSERT INTO C_DB.prvn (id, dm, name) 
SELECT (dm * 10 + id) as new_id, dm, name FROM A_DB.prvn
WHERE (A_DB.prvn.id + A_DB.prvn.dm * 10) NOT IN (SELECT id FROM C_DB.prvn)

Update #2

Another option is to let the primary key in the new table be a varchar and then build a string with fixed format that more easily would keep the pk unique assuming the column dm is unique for each of the original tables.

Then the INSERT could be changed to

INSERT INTO prvn (new_id, dm, name) 
SELECT CONCAT(dm,'-', id), dm, name FROM A_DB.prvn AS a
WHERE NOT EXISTS(SELECT * FROM C_DB.prvn WHERE id = CONCAT(a.dm,'-', a.id))

edited May 27, 2018 at 11:22

answered May 27, 2018 at 7:13

Joakim Danielson

53.1k5 gold badges36 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Carsten Massmann Over a year ago

That will achieve what OP requested. But be careful, you will most likely run into problems when you insert more than the first 10 lines, as there will most likely be non-unique values in the id column.

Joakim Danielson Over a year ago

True, the id in the new table needs to have a large enough base value to avoid clashes and the multiplier will also have to be adjusted to that.

Collectives™ on Stack Overflow

How to Insert values in mysql from other databases

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related