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

Home»Software»Databases»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

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.

Some database systems support CREATE TABLE … LIKE syntax which creates an empty table based on the definition of another table, including any column attributes and indexes defined in the original table. The difference is that CREATE TABLE … SELECT syntax also copies the data.

About the Author:

LK is a technology writer for Tech Journey with background of system and network administrator. He has be documenting his experiences in digital and technology world for over 15 years. Connect with LK through Tech Journey on Facebook, Twitter or Google+.
  • Suraj

    Awesome dude!!…. helped me bigtime… simple and effective as it could be… Thanks

  • When doing this with PostgreSQL you will have to recreate the indexes from the original table manually. The copy command will not do this for you. Does anyone know of a way to do this automatically?

  • Vijay

    Thans for sharing. It is really useful.

  • Pankaj

    Looks like the CREATE TABLE AS doesn't work in DB2. What is equivalent option in DB2 to achieve same results.

    I want to create a table using select statement based on multiple tables in DB2.

  • Mukesh

    Thanks a ton! neat trick.

  • Alberto

    Thanks a lot. Very easy and effective.

  • Thomas

    Very good and effective thanks a lot 🙂

  • Sagar

    Its Good. But it will replicate only data and table definition of old table into new table. It will not apply constraints, if any present on old table, to the new table.

Pin It on Pinterest

Share This

Share This

Share this post with your friends!