有一张城市表 city(city_id,city_name,pro_id)
一张省份表 province(pro_id,pro_name)
现在需要把这两张表中的数据合并到另外一张区域表中
t_area(pro_id,pro_name,city_id,city_name)
先用union all把select语句联系起来
SELECT CONCAT('select ''',c.`pro_id`,''',''',p.`pro_name`,''',''',c.`city_id`,''',''',c.`city_name`,''' union all')
FROM city c INNER JOIN province p
ON c.pro_id = p.`pro_id`
运行结果
把这些select语句拷贝到文本文件中,在最前面加上INSERT INTO `t_area`,同时去掉最后面的union all就可以了
INSERT INTO `t_area` SELECT '39','广东','547','广州' UNION ALL
SELECT '39','广东','548','深圳' UNION ALL
SELECT '39','广东','549','珠海' UNION ALL
SELECT '39','广东','550','汕头' UNION ALL
SELECT '39','广东','551','韶关' UNION ALL
SELECT '39','广东','552','河源' UNION ALL
SELECT '39','广东','553','梅州' UNION ALL
SELECT '39','广东','554','惠州'