CODEWITHSUNDEEP
mysql
S R R
S R R

Reputation: 61

How to Insert values in mysql from other databases

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

Upvotes: 2

Views: 78

Answers (1)

Joakim Danielson
Joakim Danielson

Reputation: 51973

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))

Upvotes: 2

Related Questions