快速复制表结构及数据

创建表，并复制数据

create table table1 as select * from table2;

只创建表结构，不复制数据

create table table1 as select * from table2 where 1=2;

复制表数据，两张表字段相同：(table1必须存在)

insert into table1 select * from table2;

复制表数据，两张表字段不相同：(table1必须存在)

insert into table1(field1,field2,field3) select field1,field2,field3 from table2;

删除表：

drop talbe table_name;

删除表数据：

truncate table table_name;

如果xxx表不存在某条数据，就从xxx表插入该条数据

insert into tb_cablecheck_dtsj select od.*, '' ro_type_id from osspad.tb_cablecheck_dtsj od where not exists (select 1 from tb_cablecheck_dtsj d where d.id = od.id)

带自增长id，需要写成子查询：

INSERT INTO tb_cablecheck_dtsj SELECT
    SEQ_CABLECHECK_DTSJ_ID.nextval,
    t.*
FROM
    (
        SELECT
            d.dzid,
            d.dzbm,
            d.sbid,
            d.sbbm,
            d.sblx,
            d.sbmc,
            d.bdsj,
            d.glbh,
            d.gdjgsj,
            d.jcsj,
            d.gxsj,
            d.areaid,
            d.sfpf,
            d.pzgh,
            d.sggh,
            d.gqgh,
            d.xz,
            d.gdbh,
            d.glmc,
            d.install_dzbm,
            d.install_sbbm,
            d.h,
            d.install_sbid,
            '' ro_type_id
        FROM
            osspad.tb_cablecheck_dtsj d,
            area a
        WHERE
            d.areaid = a.area_id
        AND a.parent_area_id = 20
        AND d.bdsj >= TO_DATE ('2016-12-01', 'yyyy-mm-dd')
        AND d.bdsj <= TO_DATE ('2016-12-31', 'yyyy-mm-dd')
    ) t

备份原表数据

create table tb_cablecheck_equipment_bak as select * from tb_cablecheck_equipment;

删除原表

drop table tb_cablecheck_equipment;

创建临时表

create table tb_equipment_20170112 as
select e.equipment_id,
       e.equipment_code,
       e.equipment_name,
       e.area_id,
       e.address,
       e.res_type_id,
       e.res_type,
       e.manage_area_id,
       e.manage_area,
       e.management_mode,
       e.isrelated,
       e.staff_id,
       e.create_date,
       e.ischecked,
       e.check_date,
       e.operate_staff,
       e.parent_area_id,
       e.grid_id,
       e.install_sbid,
       e.install_sbbm,
       e.install_dzbm,
       decode(e.longitude,null,b.longitude,e.longitude) longitude,
       decode(e.latitude,null,b.latitude,e.latitude) latitude,
       e.station_id,
       e.update_time
  from tb_cablecheck_equipment e left join tb_base_equipment b
  on e.equipment_id = b.phy_eqp_id and e.equipment_code = b.point_no

恢复原表并插入数据

create table tb_cablecheck_equipment
as
select * from tb_equipment_20170112

查询原表

select * from tb_cablecheck_equipment
select count(1) from tb_cablecheck_equipment