Create a Table Using a SQL SELECT Statement

There are several ways to create tables with special keywords such as specifying primary keys, foreign keys, unique constraints and more. One popular way to create a table is with a SELECT statement. This is a quick way to populate data into a table and can be done in several ways depending on the database of choice. We can demonstrate with some examples of how to do this.

CREATE TABLE NewTable AS (SELECT * FROM OldTable);

We can see the above statement is pretty standard of most databases. Instead of specifying the columns and datatypes we can reference a query that will be copied. The DDL will store the datatypes from the original table as well as the selected data. We can also include filters and joins in our queries.

CREATE TABLE NewTable AS (SELECT * FROM OldTable WHERE ColumnFilter = ‘Something’);

Although this may be the most common way to create a new table using a SELECT statement, it’s not the only way. Some databases use the INTO keyword instead and provide the insert and creation actions differently. For example, in SQL Server we could write our statement like the following.

SELECT * INTO NewTable FROM OldTable;

Although the syntax is slightly different, it accomplishes the same task. Be aware that there will always be slight variations between databases to accomplish the same things.

Comparing Both Methods

Both of these SQL styles do two things. First, they create a table. Second, they insert data from a select query into the table. This essentially combines two steps into one. There are several reasons you may want to do this.

For example:

• Copy data from a view into a table
• Copy a subset of data from a table into a new, smaller table
• Create a new table with complex logic already built in

As with the first method, we can apply filtering to the second method.

SELECT * INTO NewTable FROM OldTable WHERE ColumnFilter = ‘Something’;

Of course these are just some examples and we do not need to select everything from a table. We can specifically outline columns and include multiple tables. This can be done with either approach.