SELECT INTO is a handy feature if you need to move (or copy, specifically in this example) data from one table to another. This brief article means to illustrate how the same Database storage result can be achieved using multiple approaches.

Consider the following SQL INSERT statement, which puts data into TABLE 2 by first getting some data from TABLE 1 through use of a basic SQL SELECT statement. Combining SQL statements like this is standard practice, and nothing remarkable.

INSERT INTO table2(col2, col3, col7) SELECT lastname, firstname, state
FROM table1 WHERE col5 = NULL;

Employing an expanded knowledge of available SQL statements, the following illustrates that the same result as above can be achieved using more efficient, specific syntax.

Breaking down the previous expression, we might say it will effectively INSERT lastname, firstname, and state INTO TABLE 2. The data already exists and is obtained if we SELECT lastname, firstname, and state FROM TABLE 1 (a separate table) WHERE a column value is NULL.

Different tables within one database are required for the operation to complete successfully. While it’s sensible to think of two different statements for two different tables, using INSERT for TABLE 2 after SELECT from TABLE 1, the same result can be achieved with less typing! Use SELECT INTO to combine the commands from the previous expression.

Using optimized syntax (here and anywhere it makes sense to you), your code is made more portable (e.g. in this case, we typed less and will create a smaller SQL file).

The syntax of SQL SELECT INTO can vary, but one method for MySQL (MariaDB) is as follows:

SELECT INTO table2(col2, col3, col7) lastname, firstname, state
FROM table1 WHERE col5 = NULL;