Usage of temporary tables in MySQL databases – Part 2

Hi, this is part 2 of my most recent article “Usage of temporary tables in MySQL databases“. If you haven’t read it, please do before you continue. In this specific article, I’ll give you a real example of temp table usage, and hopefully, you will see its advantage.

Let’s take part of Magento EAV system database structure as an good example. The tables I’m going to use are shown in the image below (and SQL for sample schema is at the bottom of the article):

We are going to create a simple SQL for Magento catalog importer (non functional on real Magento store) using temporary table.

The logic is simple:

1. Import CSV into temp table
2. Use some SQL, read that data from temporary table and import it on right place in database

To begin, we need SQL for the creation of our temporary table. So here it is:

CREATE TEMPORARY TABLE csv_import(
id INT,
entitiy_id INT,
attribute_id INT,
VALUE TEXT
)ENGINE MyISAM

After we create it we populate it with:

INSERT INTO csv_import VALUES (1,3,2,584), (2,2,2,112), (3,1,5,"value 1"), (4,4,5,"value 2");

(it must be in the same database session / connection).

Assumption is that we have “catalog_category_entity” table populated with some sample data as follows:

entitiy_id   attribute_id
 
1            5
 
2            2
 
3            2
 
4            5

And on the other hand, we have a CSV that we inserted into “csv_import” table as foollows:

id    entitiy_id        entitiy_type_id      VALUE
 
1     3                 2                    584
 
2     2                 2                    112
 
3     1                 5                    "value 1"
 
4     4                 5                    "value 2"

And now, the fun part: populating the “catalog_category_entity_int” and “catalog_category_entity_text” tables from our temp table (“csv_import”) With 2 simple queries.

INSERT INTO catalog_category_entity_int (value_id, attribute_id, VALUE)
SELECT NULL,csvi.attribute_id,csvi.VALUE FROM csv_import AS csvi WHERE csvi.attribute_id = 2;
 
INSERT INTO catalog_category_entity_text (value_id, attribute_id, VALUE)
SELECT NULL,csvi.attribute_id,csvi.VALUE FROM csv_import AS csvi WHERE csvi.attribute_id = 5;

And all we need to do it in a batch, is to create a script that would run contain the information which attribute_id is saved in which table.

And still, this is just a simple usage (temp tables aren’t necessary for this example), but I intentionally used this one to keep both simplicity and functional example in one relatively short article.

Feel free to comment!