Generate INSERT statement from SELECT query I have two databases, in first database I have a table called zoom_city and in the second database I have a table called courrier_city. The SQL for each is below:

CREATE TABLE IF NOT EXISTS `zoom_city` (
  `id_ciudad` int(11) NOT NULL AUTO_INCREMENT,
  `id_estado` int(11) NOT NULL,
  `ciudad` varchar(200) NOT NULL,
  `capital` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id_ciudad`),
  KEY `id_estado` (`id_estado`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=644;

CREATE TABLE IF NOT EXISTS `courrier_city` (
  `country` varchar(4) NOT NULL,
  `state` varchar(10) NOT NULL,
  `id` varchar(100) NOT NULL,
  `name` varchar(255) NOT NULL,
  `capital` varchar(255) NOT NULL,
  `created` timestamp NULL DEFAULT NULL,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deletedAt` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`country`,`state`,`id`),
  KEY `fk_courrier_city_state1_idx` (`country`,`state`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


So I need to dump values from first table zoom_city and insert them in courrier_city but taking this in account:


country always be VE string
id_estado are stored in zoom_city as for example 1, 2, 3, 10 and right values for courrier_city and taking same example would be VE.01, VE.02, VE.03 and VE.10 so this value needs a transform before insertion.


Is that possible in raw SQL or I need to do this in a programming language like PHP for example?
I wasn't clear where you want the transformed id_estado inserted, so I chose "state" as a destionation.   You can modify accordingly:

insert into courrier_city (country, state, created, modified) select "VE", if(is_estado&lt;10, concat("VE0.",is_estado),concat("VE.",is_estado)), now(), now() from zoom_city


You will probably want to modify the example further to account for specifying all the destination columns with either a literal value such we did for country, a transform like we did for is_estado, or just the basic column name in the select portion to copy values.

You should specify some value for every destination column without a schema default defined.