Hola buenas! Estoy intentando unir varias tablas en una sola, pero no estoy muy seguro de como hacerlo. La estructura de las tablas es la siguiente:
-- tablas: en, fr, de, zh_cn, es, ru, pt_br
`geoname_id` INT (11),
`continent_code` VARCHAR (200),
`continent_name` VARCHAR (200),
`country_iso_code` VARCHAR (200),
`country_name` VARCHAR (200),
`subdivision_1_name` VARCHAR (200),
`subdivision_2_name` VARCHAR (200),
`city_name` VARCHAR (200),
`time_zone` VARCHAR (200)
Son 7 tablas (cada una un idioma diferente) y me gustaría juntarlas todas en una sola, añadiendo como prefijo el código del pais delante de los nombres de las columnas.
Esta es la estructura de la nueva tabla, donde el contenido de las demás sera añadido:
CREATE TABLE `geo_lists` (
`city_id` INT (11), -- en.geoname_id (same for all 7 tables)
`continent_code` VARCHAR (2), -- en.continent_code (same for all 7 tables)
`continent_name` VARCHAR (200), -- en.continent_name (just in english)
`country_code` VARCHAR (2), -- en.country_iso_code (same for all 7 tables)
`en_country_name` VARCHAR (200), -- en.country_name
`fr_country_name` VARCHAR (200), -- fr.country_name
`de_country_name` VARCHAR (200), -- de.country_name
`zh_country_name` VARCHAR (200), -- zh_cn.country_name
`es_country_name` VARCHAR (200), -- es.country_name
`ru_country_name` VARCHAR (200), -- ru.country_name
`pt_country_name` VARCHAR (200), -- pt_br.country_name
`en_state_name` VARCHAR (200), -- en.subdivision_1_name
`fr_state_name` VARCHAR (200), -- fr.subdivision_1_name
`de_state_name` VARCHAR (200), -- de.subdivision_1_name
`zh_state_name` VARCHAR (200), -- zh_cn.subdivision_1_name
`es_state_name` VARCHAR (200), -- es.subdivision_1_name
`ru_state_name` VARCHAR (200), -- ru.subdivision_1_name
`pt_state_name` VARCHAR (200), -- pt_br.subdivision_1_name
`en_province_name` VARCHAR (200), -- en.subdivision_2_name
`fr_province_name` VARCHAR (200), -- fr.subdivision_2_name
`de_province_name` VARCHAR (200), -- de.subdivision_2_name
`zh_province_name` VARCHAR (200), -- zh_cn.subdivision_2_name
`es_province_name` VARCHAR (200), -- es.subdivision_2_name
`ru_province_name` VARCHAR (200), -- ru.subdivision_2_name
`pt_province_name` VARCHAR (200), -- pt_br.subdivision_2_name
`en_city_name` VARCHAR (200), -- en.city_name
`fr_city_name` VARCHAR (200), -- fr.city_name
`de_city_name` VARCHAR (200), -- de.city_name
`zh_city_name` VARCHAR (200), -- zh_cn.city_name
`es_city_name` VARCHAR (200), -- es.city_name
`ru_city_name` VARCHAR (200), -- ru.city_name
`pt_city_name` VARCHAR (200), -- pt_br.city_name
`time_zone` VARCHAR (30) -- en.time_zone (same for all 7 tables)
);
Así lo que estoy intentando hacer, pero algo va mal:
INSERT INTO geo_lists
-- columns
(city_id, continent_code, continent_name, country_code,
en_country_name,
fr_country_name,
de_country_name,
zh_country_name,
es_country_name,
ru_country_name,
pt_country_name,
en_state_name,
fr_state_name,
de_state_name,
zh_state_name,
es_state_name,
ru_state_name,
pt_state_name,
en_province_name,
fr_province_name,
de_province_name,
zh_province_name,
es_province_name,
ru_province_name,
pt_province_name,
en_city_name,
fr_city_name,
de_city_name,
zh_city_name,
es_city_name,
ru_city_name,
pt_city_name,
time_zone)
-- end columns
SELECT
en.geoname_id, en.continent_code, en.continent_name, en.country_iso_code,
en.country_name AS en_country_name,
fr.country_name AS fr_country_name,
de.country_name AS de_country_name,
zh_cn.country_name AS zh_country_name,
es.country_name AS es_country_name,
ru.country_name AS ru_country_name,
pt_br.country_name AS pt_country_name,
en.subdivision_1_name AS en_state_name,
fr.subdivision_1_name AS fr_state_name,
de.subdivision_1_name AS de_state_name,
zh_cn.subdivision_1_name AS zh_state_name,
es.subdivision_1_name AS es_state_name,
ru.subdivision_1_name AS ru_state_name,
pt_br.subdivision_1_name AS pt_state_name,
en.subdivision_2_name AS en_province_name,
fr.subdivision_2_name AS fr_province_name,
de.subdivision_2_name AS de_province_name,
zh_cn.subdivision_2_name AS zh_province_name,
es.subdivision_2_name AS es_province_name,
ru.subdivision_2_name AS ru_province_name,
pt_br.subdivision_2_name AS pt_province_name,
en.city_name AS en_city_name,
fr.city_name AS fr_city_name,
de.city_name AS de_city_name,
zh_cn.city_name AS zh_city_name,
es.city_name AS es_city_name,
ru.city_name AS ru_city_name,
pt_br.city_name AS pt_city_name,
en.time_zone
FROM en, fr, de, zh_cn, es, ru, pt_br
WHERE en.geoname_id = fr.geoname_id
AND fr.geoname_id = de.geoname_id
AND de.geoname_id = zh_cn.geoname_id
AND zh_cn.geoname_id = es.geoname_id
AND es.geoname_id = ru.geoname_id
AND ru.geoname_id = pt_br.geoname_id
El problema es que nunca termina de ejecutarse... Estoy usando SQLYog Community Edition en Windows 8.1. Lleva casi 1 hora ejecutandose y nada, los archivos csv solo pesan 42mb todas (no se cuanto pesaran ahora que estan metidos en tablas - usando LOAD DATA INFILE)
Gracias!
Edito para añadir estructura de la nueva tabla, y como se juntan todas en la nueva.
Vale el problema es que se me olvido poner primary key para la primera columna (id).