Apr 28, 2021 by Robert Gravelle

There are many times where one needs to copy data from an existing table to a new one, for example, to back up data or to replicate data in one environment in another, as one might do for testing purposes. In SQL, one would typically use CREATE TABLE and SELECT statements as follows:

CREATE TABLE new_table; 
SELECT SELECT col, col2, col3 
INTO new_table 
FROM
    existing_table;

In the first statement, the database creates a new table with the name indicated in the CREATE TABLE statement. The structure of the new table is defined by the result set of the SELECT statement. Then, the database populates data with the results of the SELECT statement to the new table.

While the above procedure works perfectly well, there's an easier way to copy a table into a new one using a variation of the CREATE TABLE statement! We'll learn how to use it here today.

Introducing the CREATE TABLE AS SELECT Statement

The CREATE TABLE statement provides a way to create one table from another by adding a SELECT statement at the end of the CREATE TABLE statement. The full syntax for the statement is:

CREATE TABLE new_tbl [AS] SELECT * FROM orig_tbl;

It's a way to do, in one line of code, the exact same thing as we did using two separate statements above.

Copying Partial Data

Since the SELECT statement support all clauses that you'd usually employ in your SQL statements, including the WHERE and ORDER BY clauses, we can limit what we copy over by supplying a condition in our statement. Here's the syntax for that:

CREATE TABLE new_table 
SELECT col1, col2, col3 
FROM
    existing_table
WHERE
    conditions;

Some Examples

Here are a couple of examples using Navicat Premium as our database client:

In its most basic form, the CREATE TABLE AS SELECT statement can copy a table "as-is" using a SELECT All (*). Here's an example:

Here's a more complex example that only copies three columns from an orders table and limits rows to those with a recent requiredDate:

We can see that the new table only has three columns a selected:

Conclusion

There's no question that the CREATE TABLE AS SELECT statement offers a quick and easy way to copy data from a table into a new one. Having said that, it does have its limitations. For starters, not all relational databases support it. I know that MySQL and SQL Server do, but other databases may or may not.

It is also worth noting that the CREATE TABLE AS SELECT statement just copies the table and its data. It does not copy other database objects such as indexes, primary key constraint, foreign key constraints, triggers, etc., associated with the table. To copy not only the data but also all database objects associated with a table, we should use two separate statements as follows:

CREATE TABLE orders_copy LIKE orders;

INSERT orders_copy
SELECT * FROM orders;

Interested in Navicat Premium? You can try it for 14 days completely free of charge for evaluation purposes!