526互联

SQL: duplicate table

发布时间 2023-04-30 14:17:52作者: ascertain

 

 

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. 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");