I needed to take identical tables on 3 databases, and merge it onto a new table on a new database. Clearly the reason for the task was consolidation. My biggest complaint about mysql is its lack of linked servers. You can do federated, but lets be honest….It’s a pain in the %$#^
So when it comes to consolidating, the best way (at least in my situation) was to manually merge the data. I thought of a few ways this could be done, but here is how I ended up doing it.
1) DUMP DATA to a File.
select broker_description,date,type,
case when type = "O" then abs(Shares*100)
else abs(Shares) end as Quantity,
commission,'someportfolio' as portfolio
from accounting.trades r
join tickers t
on t.id = r.ticker
join exec_broker b
on b.id = r.exec_broker
where abs(commission > 0)
INTO OUTFILE "/tmp/dump.txt"
2) SCP the dump file from server1 to server2 (server where merge table will be)
scp /tmp/dump.txt root@10.1.10.20:/tmp/
3) IMPORT DATA (this assumes you scp’d the file to /tmp/ on the merge table server)
LOAD DATA INFILE '/tmp/dump.txt'
INTO TABLE commission
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
(broker_description, date, type,Quantity,commission,portfolio);
NOT NECESSARY FROM HERE DOWN>>>>>THIS WAS AN EXTRA STEP
4) Dump server3 table data to file
In this case I actually needed to dump data from a mysql database that was on a Windows Server. Similar to step 1.
select broker_description,date,type,
case when type = "O" then abs(Shares*100)
else abs(Shares) end as Quantity,
commission,'portfolio2' as portfolio
from accounting.trades r
join tickers t
on t.id = r.ticker
join exec_broker b
on b.id = r.exec_broker
where abs(commission > 0)
INTO OUTFILE "c:/temp/dump.txt"
5) I used WinSCP to upload the file from c:\temp to /tmp on target server
Greg MySQL MySQL