Import values from temporary table to another table?

Question

I'm joining 3 tables:

SELECT 
catalog_product_entity.entity_id AS product_id,
catalog_product_entity.sku,
customer_entity_varchar.entity_id AS customer_id,
customer_entity_varchar.value,
customer_entity_varchar.attribute_id,
import_tmp.customer,
import_tmp.grp,
import_tmp.qty,
import_tmp.price
FROM catalog_product_entity
    INNER JOIN import_tmp 
    ON catalog_product_entity.sku = import_tmp.sku
    INNER JOIN customer_entity_varchar 
    ON import_tmp.customer = customer_entity_varchar.value OR import_tmp.grp = customer_entity_varchar.value
    WHERE customer_entity_varchar.attribute_id = 139 OR customer_entity_varchar.attribute_id = 140

Currently, I'm fetching the data, build a new array and insert it into my final table.

INSERT INTO final_table(`c_id`, `p_id`, `price`) VALUES (customer_id, product_id, price);

However, I wonder if it's possible to do this in one go, so basically directly from the temporary table?

There is an ANSI SQL approach for this which you could look at https://stackoverflow.com/questions/25969/insert-into-values-select-from ... a word of caution though. Make sure this is done outside of a production environment first. Clone a portion of all your tables to a local database or somewhere safe. Never run something like this directly in production. — hppycoder, Mar 24 '21 at 13:24

score 0 · Accepted Answer · answered Mar 24 '21 at 13:23

0

Yes. Use INSERT . . . SELECT:

INSERT INTO final_table (c_id, p_i`, price)
    SELECT cev.entity_id AS customer_id,
           cpe.entity_id AS product_id,
           it.price
    FROM catalog_product_entity cpe JOIN
         import_tmp it
         ON cpe.sku = it.sku JOIN
         customer_entity_varchar cev
         ON cev.value IN (it.customer, it.grp)
    WHERE cev.attribute_id IN (139, 140);

Note that I introduced table aliases. These make the query easier to write and to read. And IN is easier to follow than repeated OR conditions.

answered Mar 24 '21 at 13:23

Gordon Linoff

1,242,037
58
646
786

Just one follow up question, in case I'll ever need it. Is it possible to INSERT ... SELECT into 2 tables? So basically into final_table_A and final_table_B ? – hardy123480 Mar 24 '21 at 14:17
@hardy123480 . . . MySQL doesn't support inserting into two tables in one `INSERT`. – Gordon Linoff Mar 24 '21 at 16:37

score 0 · Answer 2 · answered Mar 24 '21 at 13:25

INSERT INTO final_table(`c_id`, `p_id`, `price`) 

SELECT 
customer_entity_varchar.entity_id AS customer_id,
catalog_product_entity.entity_id AS product_id,
import_tmp.price
FROM catalog_product_entity
    INNER JOIN import_tmp 
    ON catalog_product_entity.sku = import_tmp.sku
    INNER JOIN customer_entity_varchar 
    ON import_tmp.customer = customer_entity_varchar.value OR import_tmp.grp = customer_entity_varchar.value
    WHERE customer_entity_varchar.attribute_id = 139 OR customer_entity_varchar.attribute_id = 140

Above code will do that. Just select to rows you want to insert into final_table.

Import values from temporary table to another table?

2 Answers2