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

In Oracle, PostgreSQL, DB2 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.

Related Articles

• Create New Table by Selecting Data from Other Tables with CREATE TABLE AS
• ORA-02449 Oracle Drop Table Error
• ORA-00942 Table or View Does Not Exist Oracle Error
• Oracle ORA-14074 Create or Add New Partition Fails Error
• Create, Add or Split Oracle Database Partition Fails with ORA-14080 Error
• Oracle PL/SQL ORA-00947 Not Enough Values Error
• Oracle ORA-01658 Unable to Create INITIAL Extent for Segment in Tablespace Error
• Export, Backup or Copy Out the IE7 Search Providers List
• ORA-01502 Oracle Index in Unusable State
• ORA-25153 Temporary Tablespace is Empty Error in Oracle

This entry was posted on Sunday, March 18th, 2007 at 10:35 pm and is filed under Databases. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.