Duplicate, Copy or Backup Tables in MySQL, MariaDB, Oracle, PostgreSQL, DB2 and SQLite with Create Table As SQL

In Oracle, PostgreSQL, DB2, MySQL, MariaDB and SQLite database system, there is a nice command feature called Create Table As which allows easy duplicating of a table with data from another or a few other tables. The SQL command can also be used to ‘copy and paste’ a table exactly or backup a table data before performing data manipulation query on the original table, just in case the script has error and the backup date can be used to restore to original state. Create Table As will creates a new table built from the content of dataset or result set retrieved by a Select SQL query from a table that already exists within the database.

The syntax of Create Table As SQL statement is:

CREATE TABLE table_name [ ( column_name [, ...] ) ]
             AS select [ * ! ( column_name [, ...] ) ] FROM source_table_name

Replace table_name with the name of the new table that will be created. Column name is optional, where you can specify multiple columns by including their names in a comma-delimited list. Else, the structure of the new table will be based on the column names, types and number of columns returned by the Select statement, together with the row data. If you specify the column name, note that there should be the same number of columns specified as are returned by select.

The select statement at the end of create table as command must be valid, and has the number of targets selected matching the number of columns in the optional column list preceding the AS clause. It can be a complex select statement that retrieve data from multiple tables. If optional column list is specified within parentheses, asterisk (*) can no longer be used in the select statement.

For example,

CREATE TABLE demo_backup
AS SELECT * FROM demo;

Above SQL statement will create a exact replica backup table named demo_backup with data and structure (columns) of demo table.

Possible error if you specify the optional column list is:

ERROR: CREATE TABLE/AS SELECT has mismatched column count

If you encounter this error message, this is due to optional list of columns in parentheses contains a different number of rows than the select statement returns. Double check if the number of columns specified is the same with the results that are expected from the select resultset.