SQL: CREATE TABLE … SELECT … to generate new table from existing tables

• Tweet

• Tweet

CREATE TABLE … SELECT comes rather useful when you need to generate reports from existing tables or do a mixture. The title just shows the basic usage of it, for instance, to create a new table xxx from 2 columns in yyy, namely col1, col2.

After executing SQL:

CREATE TABLE xxx SELECT col1, col2 FROM yyy

All the records in yyy with fields col1 and col2 is thus now copied to the new table xxx.

A blending mixture of various columns, stats of columns from various different tables can be then mingled and combined into one table upon your need. Consider the examples below.

CREATE TABLE mingle1 SELECT table1.col1, table1.col3, table2.col2, table2.col6 FROM table1, table2

This query selects different columns from 2 tables into one table mingle1.

CREATE TABLE stats1 SELECT table1.col1, COUNT(table2.col2) WHERE table2.col3 = table1.col1 GROUP BY table2.col3

This query does a little statistic job by counting the number of all unique table2.col2 records on each table1.col1 and put the data into a new table stats1.