[Oracle] select문으로 insert하기(row_number() 이용)

1. insert문에 들어가야 할 컬럼을 select문으로 조회한다.

select (select max(fileno)+1 from tb_file_data) + row_number() over(order by t.fileno) as fileno
         , t.cntno, 'public', t.orifilename, t.filename, t.filetype, t.filesize
from TB_FILE_DATA t
where  code like '%open%'
and delchk = 'N'

 

 

2. pk의 무결성제약조건을 만족시키기 위하여 select문으로 조회한 순번만큼 pk값을 더해준다.

(select max(fileno)+1 from tb_file_data) + row_number() over(order by t.fileno)

 

3. insert문을 생성한다.

insert into TB_FILE_DATA (fileno, cntno, code, orifilename, filename, filetype, filesize)
 select (select max(fileno)+1 from tb_file_data) + row_number() over(order by t.fileno) as fileno
         , t.cntno, 'public', t.orifilename, t.filename, t.filetype, t.filesize
from TB_FILE_DATA t
where  code like '%open%'
and delchk = 'N'