Creating Tables using Existing tables - WITH DATA/WITH NO DATA

Creating Tables using the WITH DATA/WITH NO DATA syntax

Creating Empty table from existing tables:

Following is the syntax of creating empty tables from existing tables:

CREATE TABLE newtable AS oldtable WITH NO DATA;

WITH NO DATA syntax indicates that data should not be copied.

Creating tables from existing tables with data:

Following is the syntax for creating exact copy of the existing table along with the data.

CREATE TABLE newtable AS oldtable WITH  DATA;

What all properties are copied to the new table using the above two syntax?

• Column names

• Column Data types

• Default values

• NOT NULL constraints
• UNIQUE constraints

• CHECK constraints

• PRIMARY KEY constraints
• Indexes (primary , secondary)

• Fallback options

What all properties are not copied to new table?

• Referential integrity

• Stats

To copy stats we need to use the below syntax:

CREATE TABLE newtable AS oldtable WITH NO DATA AND STATS;

Note that NO only applies to DATA.

Thus above query will create a table copy with out table data , but will define same stats on the new table also.

We can change the names of the columns and their attributes also when creating tables using the above syntax:

CREATE TABLE newtable (

COL1 UNIQUE NOT NULL,

COL2 PRIMARY KEY NOT NULL,

COL3)

AS oldtable WITH DATA;