SQL: duplicate table

 

 

MySQL

1. create table new_table select c1,c2 from old_table [where 1=2]

   lose index, auto_increment

2. create table new_table like old_table

   table structure is exactly the same, no data

3. insert into new_table select * from old_table
   insert into new_table (c1,c2) select (c1,c2) from old_table

   just copy data, new_table must exist

 

 

PostgreSQL

1. create table new_table as table old_table [ with no data ]
   create table new_table as select * from old_table where condition

   All the statements above copy table structure and data do not copy indexes and constraints
   
   

 

sqlite3

1. create table new_table as select * from old_table where 0

    

2. insert into new_table select * from old_table

    

3. select sql from sqlite_master where type='table' and name='old_table'

    

4. sqlite3 dbfile '.schema oldtable' | sed '1s/oldtable/newtable/' | sqlite3 dbfile
   sqlite3 dbfile '.schema newtable'

   CREATE TABLE mytable (
       contact_id INTEGER PRIMARY KEY,
       first_name TEXT NOT NULL,
       last_name TEXT NOT NULL,
       email TEXT NOT NULL UNIQUE,
       phone TEXT NOT NULL UNIQUE
   );
   
   -- Two variations
   INSERT INTO mytable VALUES ( 1, "Donald", "Duck", "noone@nowhere.com", "1234");
   INSERT INTO mytable ( contact_id,first_name,last_name,email,phone ) VALUES ( 2, "Daisy", "Duck", "daisy@nowhere.com", "45678");