Difference between Create table (copy) and Create table (select)

Difference between Create table (copy) and Create table (select)

Difference between Create table (copy) and Create table (select)

When ever we need to create a copy of existing table we tend to use create table(copy ) from existing table or Create table ( select) from existing table.

Many may ignore the difference in running of create table in two different ways assuming the structure created to  be same.  But in actual case, it is  not so!!

let us try out two type of create table types using examples to understand the differences.

Create a table Check123 which include not null ,default ,UPI and USI definations in it

SHOW   TABLE check123;

/*

CREATE SET TABLE check123 ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT

     (

      int1 INTEGER DEFAULT 0 ,

     int12 INTEGER NOT NULL DEFAULT 0 ,

      int2 INTEGER NOT NULL,

      int3 INTEGER NOT NULL)

UNIQUE PRIMARY INDEX prim1 ( int3 )

UNIQUE INDEX uniq1 ( int2 );

*/

Step1:   Create table Check_COPY  from Check123 using CREATE TABLE (COPY ) method

CREATE   TABLE check_COPY AS check123 WITH no data ;

Run show table command  to check for table structure

SHOW   TABLE check_COPY;

/*

CREATE SET TABLE  check_COPY ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT

     (

      int1 INTEGER DEFAULT 0 ,

      int12 INTEGER NOT NULL DEFAULT 0 ,

      int2 INTEGER NOT NULL,

      int3 INTEGER NOT NULL)

UNIQUE PRIMARY INDEX prim1 ( int3 )

UNIQUE INDEX uniq1 ( int2 );

*/

From the following observation we can understand that the table created using COPY method will retain all datatypes and index definations like UPI and NUPI

Step2:   Create table Check_SELECT  from Check123 using CREATE TABLE (COPY ) method

CREATE      TABLE Check_SELECT AS

( sel * FROM   check123 ) WITH no data ;

Run show table command  to check for table structure

SHOW   TABLE Check_SELECT;

/*

CREATE SET TABLE Check_SELECT ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT

     (

      int1 INTEGER,                          --DEFAULT MISSING

      int12 INTEGER,                -- DEFAULT and NOTNULL  MISSING

      int2 INTEGER,                          -- NOTNULL  MISSING

      int3 INTEGER)                          -- NOTNULL  MISSING

PRIMARY INDEX ( int1 );

 */

Hence  when table is created using  CREATE TABLE using SELECT from table method, the table created will not retain following from original table

·                     DEFAULT

·                     NOT NULL

·                     UNIQUE PRIMARY INDEX

·                     UNIQUE INDEX