Share This Article:

Subscribe to Kavoir:

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.

Share This Article:

Subscribe to Kavoir:

You should also read:

• SQL: INSERT INTO … SELECT … to generate or combine records from existing ones and insert into an existing table
• MySQL: Deleting Duplicate Rows or Records | Selecting Unique Rows into A New Table
• MySQL: INSERT INTO … SELECT … FROM … Selectively on Table Columns / Fields to Combine Multiple Tables into One
• Using SQL to Find records existing in one table but not in another
• SQL: Randomly Shuffle Rows or Records – Reorder them in a random order