关于表自身的复制

[smartlylife]

create table BIL_WORKTEMPLET
(
TEMPID VARCHAR2(36) not null,(主键)
TEMPTYPE NUMBER not null,
WORKTYPE VARCHAR2(1) not null,
TASK VARCHAR2(256) not null,
GZTJ VARCHAR2(2048),
AQCS1 VARCHAR2(2048),
AQCS2 VARCHAR2(2048)
)
create table BIL_WORKTEMP7CONT
(
CONTID VARCHAR2(36) not null,(主键)
TEMPID VARCHAR2(36) not null,(外键)
STEP NUMBER not null,
ITEM VARCHAR2(512) not null
)

现在我想复制比如:tempid='guid'的记录还是到这两个表中SQL要如何写(单表复制主键使用guid即可,两个有关联的表如何同时复制就不知道了),我想用纯SQL实现不用存储过程,谢谢

[hevin]

不知这样行不行，没试过，楼主可以试一试:
INSERT ALL
INTO BIL_WORKTEMPLET (TEMPID,TEMPTYPE,WORKTYPE,TASK,GZTJ,AQCS1,AQCS2)
Valuse (a.TEMPID,a.TEMPTYPE,a.WORKTYPE,a.TASK,a.GZTJ,a.AQCS1,a.AQCS2)
INTO BIL_WORKTEMP7CONT (a.TEMPID,b.CONTID,b.STEP,b.ITEM )
SELECT a.TEMPID,a.TEMPTYPE,a.WORKTYPE,a.TASK,a.GZTJ,a.AQCS1,a.AQCS2,
b.CONTID,b.STEP,b.ITEM
FROM BIL_WORKTEMPLET a join BIL_WORKTEMP7CONT b on a.TEMPID=b.TEMPID
WHERE a.TEMPID = 'guid'

[smartlylife]

谢谢你,又学到一招

[hevin]

客气