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

Navigation

#1 by (4 votes)

2

I'm running the following 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 into the target table.

Destination table details:

Name:
```
cart_details
```

Fields:

'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 inserir

asked by anonymous 24.12.2013 / 18:27

1 answer

4

In MySQL, to insert the result that we get from a query to the database in a table of the same database, just precede the query that 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

24.12.2013 / 18:27

How can I convert a DataTable to a dynamic object? Transform Rotation with Unity3D