1

I've got over 2000 databases that may or may not have the same tables, but in the cases where they do, they should have the same type of information. From one table (settings) I'm trying to get the rows that have the names:

email
client_id
client_name

Then from a second table (Cluster) I'm trying to just add up the number of rows.

I'm stuck at even getting just the email for all of them. Trying to do the following with every single database listed, it tells me it can only join a certain number at once. I'm not opposed to running the command multiple times to eventually get the information, but then it tells me that "name" is too ambiguous.

SELECT * 
FROM  `database1`.`settings`,
`database2`.`settings`,
`database3`.`settings`,
`database4`.`settings`
WHERE (
CONVERT(  `name` 
USING utf8 ) LIKE  '%email%'
)
LIMIT 0 , 30

Ideally, I want to be able to have email, client_id, client_name, and the Cluster row totals associated so I can import it into a single database later.

Any help is appreciated!

0

I would start by using information_schema.TABLES and COLUMNS to find which databases (table_schema) has what tables with the desired columns.

Then I would construct (via SELECT CONCAT("SELECT ", ...)...) the SELECT statements that contain references to all the tables/columns desired.

From them, copy and paste to run them. Or put all of this in a Stored Procedure so that a single CALL can do all the work including executeing the constructed SELECTs.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.