MySQL Cookbook

Using Temporary Tables for Data Transformation

Problem

You want to preprocess input data for MySQL, but you don’t have access to external utilities for doing so.

Solution

Load the data into a temporary table, reformat it using SQL statements, then copy the records into the final destination table.

Discussion

To work with information that must be checked or transformed before it’s ready to be added to a table, it’s sometimes helpful to load a datafile into a temporary table first for validation purposes. (It’s generally easier to work with a dataset that is isolated into its own table rather than combined with other records.) After you have made sure that the temporary table’s contents are satisfactory, copy its rows to the main table and then drop it. (Note that the use of “temporary” in this context doesn’t necessarily imply that you must use the keyword TEMPORARY when creating the table.^[47] If you process the table in multiple phases over the course of several server connections, you’ll need to create a non-TEMPORARY table, then drop it explicitly when you’re done with it.)

The following example illustrates how to use a temporary table to solve a common problem: loading data into a table when the values do not have the format required by the table structure. Suppose you have a table main that contains three columns, name, date, and value, where date is a DATE column requiring values in ISO format (CCYY-MM-DD). Suppose also that you’re given a datafile newdata.txt to be imported ...