How can I insert the results of a query into another table?

Asked

Viewed 644 times

2

I am executing the following database query directly from the phpMyAdmin:

SELECT 
  shop.id AS cart_details_id,
  shop.id AS cart_id,
  e.name AS client_name,
  e.nif AS client_tin,
  e.addr AS client_address,
  CONCAT (e.zipcode, ' ', e.zipcodeext) AS client_zipcode,
  e.zipcodename AS client_location,
  c.printable_name_prt AS client_country,
  e.contact AS client_phone,
  CONCAT('') AS client_cell,
  e.email AS client_email,
  CONCAT('') AS notes,
  shop.end_time AS date_created,
  shop.end_time AS date_updated
FROM table_eshop shop
INNER JOIN entities e ON (e.id = shop.uid)
INNER JOIN system_table_countries c ON (c.id = e.country_id)
WHERE shop.id != ''
ORDER BY shop.id ASC

The results all appear well on the screen, but I would like to insert them in the target table.

Details of the destination table:

  • Name:

    cart_details

  • Campos:

    `cart_details_id`, `cart_id`, `client_name`, `client_tin`, `client_address`,
`client_zipcode`, `client_location`, `client_country`, `client_phone`, `client_cell`,
`client_email`, `notes`, `date_created`, `date_updated`

Question

How can I insert the results of a query into another table?

mysql query insert

  • @User I voted negative: An explanation for the negative vote would help me to understand what is wrong with the question in order to improve it.

1 answer

4


In Mysql, to insert the result we get from a database query in a table of the same database, just precede the query we will perform with the insertion line.

Link to documentation. (English)

For your particular case:

INSERT INTO `cart_details` ( `cart_details_id` , `cart_id` , `client_name` , `client_tin` , `client_address` , `client_zipcode` , `client_location` , `client_country` , `client_phone` , `client_cell` , `client_email` , `notes` , `date_created` , `date_updated` )
SELECT
  shop.id AS cart_details_id,
  shop.id AS cart_id,
  e.name AS client_name,
  e.nif AS client_tin,
  e.addr AS client_address,
  CONCAT( e.zipcode, ' ', e.zipcodeext ) AS client_zipcode,
  e.zipcodename AS client_location,
  c.printable_name_prt AS client_country,
  e.contact AS client_phone,
  CONCAT( '' ) AS client_cell,
  e.email AS client_email,
  CONCAT( '' ) AS notes,
  shop.end_time AS date_created,
  shop.end_time AS date_updated
FROM table_eshop shop
INNER JOIN entities e ON ( e.id = shop.uid )
INNER JOIN system_table_countries c ON ( c.id = e.country_id )
WHERE shop.id != ''
ORDER BY shop.id ASC

Browser other questions tagged mysql query insert

You are not signed in. Login or sign up in order to post.