創建表，并復制數據

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.glmc,

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.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

與50位技術專家面對面20年技術見證，附贈技術全景圖

總結

以上是生活随笔為你收集整理的oracle数据库复制mysql,MySQL/Oracle数据库，使用SQL快速复制表结构或数据的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。