데이터베이스 질문과 답변
오라클 다중 insert
0
2
2,489
by 준일정 [2023.11.03 14:55:21]
INSERT INTO TB_CMM_SRVY_DGSTFN_TYPE (
DGSTFN_TYPEID
, QESTNRID
, DGSTFN_TYPE_CD
, DGSTFN_TYPE_NM
, ORDR
, REG_DT
, RGTRID
) VALUES
((SELECT MAX(DGSTFN_TYPEID)+1 FROM TB_CMM_SRVY_DGSTFN_TYPE)
, '124101'
, '교육만족도'
, 141
, 1
, SYSDATE
, '10000001')
,
((SELECT MAX(DGSTFN_TYPEID)+1 FROM TB_CMM_SRVY_DGSTFN_TYPE)
, '124102'
, '강사만족도'
, 141
, 2
, SYSDATE
, '10000001')
,
((SELECT MAX(DGSTFN_TYPEID)+1 FROM TB_CMM_SRVY_DGSTFN_TYPE)
, '124103'
, '시설만족도'
, 141
, 3
, SYSDATE
, '10000001')
,
((SELECT MAX(DGSTFN_TYPEID)+1 FROM TB_CMM_SRVY_DGSTFN_TYPE)
, '124104'
, '운영만족도'
, 141
, 4
, SYSDATE
, '10000001')
,
((SELECT MAX(DGSTFN_TYPEID)+1 FROM TB_CMM_SRVY_DGSTFN_TYPE)
, '124105'
, '수강후기'
, 141
, 5
, SYSDATE
, '10000001')
다중인설트 하는데
사진처럼 에러가 뜹니다 끝에 ;(세미콜론)붙여서도 해도 안되는데 왜 이런걸가요?
oracle(Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production)입니다.
by pajama
[2023.11.03 16:11:00]
여러건을 한번에 INSERT 하신다면 INSERT ALL을 사용하시면 됩니다.
http://www.gurubee.net/lecture/2688
INSERT ALL
INTO TB_CMM_SRVY_DGSTFN_TYPE (DGSTFN_TYPEID, QESTNRID, DGSTFN_TYPE_CD, DGSTFN_TYPE_NM, ORDR, REG_DT, RGTRID) VALUES
((SELECT MAX(DGSTFN_TYPEID)+1 FROM TB_CMM_SRVY_DGSTFN_TYPE), '124101', '교육만족도', 141, 1, SYSDATE, '10000001')
INTO TB_CMM_SRVY_DGSTFN_TYPE (DGSTFN_TYPEID, QESTNRID, DGSTFN_TYPE_CD, DGSTFN_TYPE_NM, ORDR, REG_DT, RGTRID) VALUES
((SELECT MAX(DGSTFN_TYPEID)+1 FROM TB_CMM_SRVY_DGSTFN_TYPE), '124102', '강사만족도', 141, 1, SYSDATE, '10000001')
SELECT 1 from dual
by 마농
[2023.11.06 00:49:51]
컬럼 순서가 안맞는 느낌이 드네요.
141 이 qestnrid 일 것 같습니다.
INSERT INTO tb_cmm_srvy_dgstfn_type
( dgstfn_typeid
, qestnrid
, dgstfn_type_cd
, dgstfn_type_nm
, ordr
, reg_dt
, rgtrid
)
SELECT dgstfn_typeid + ordr
, 141
, dgstfn_type_cd
, dgstfn_type_nm
, ordr
, sysdate
, '10000001'
FROM (SELECT MAX(dgstfn_typeid) dgstfn_typeid
FROM tb_cmm_srvy_dgstfn_type
) a
, (SELECT 1 ordr, '124101' dgstfn_type_cd, '교육만족도' dgstfn_type_nm FROM dual
UNION ALL SELECT 2, '124102', '강사만족도' FROM dual
UNION ALL SELECT 3, '124103', '시설만족도' FROM dual
UNION ALL SELECT 4, '124104', '운영만족도' FROM dual
UNION ALL SELECT 5, '124105', '수강후기' FROM dual
) b
;
INSERT ALL
INTO tb_cmm_srvy_dgstfn_type(dgstfn_typeid, qestnrid, dgstfn_type_cd, dgstfn_type_nm, ordr, reg_dt, rgtrid)
VALUES(dgstfn_typeid + 1, qestnrid, '124101', '교육만족도', 1, reg_dt, rgtrid)
INTO tb_cmm_srvy_dgstfn_type(dgstfn_typeid, qestnrid, dgstfn_type_cd, dgstfn_type_nm, ordr, reg_dt, rgtrid)
VALUES(dgstfn_typeid + 2, qestnrid, '124102', '강사만족도', 2, reg_dt, rgtrid)
INTO tb_cmm_srvy_dgstfn_type(dgstfn_typeid, qestnrid, dgstfn_type_cd, dgstfn_type_nm, ordr, reg_dt, rgtrid)
VALUES(dgstfn_typeid + 3, qestnrid, '124103', '시설만족도', 3, reg_dt, rgtrid)
INTO tb_cmm_srvy_dgstfn_type(dgstfn_typeid, qestnrid, dgstfn_type_cd, dgstfn_type_nm, ordr, reg_dt, rgtrid)
VALUES(dgstfn_typeid + 4, qestnrid, '124104', '운영만족도', 4, reg_dt, rgtrid)
INTO tb_cmm_srvy_dgstfn_type(dgstfn_typeid, qestnrid, dgstfn_type_cd, dgstfn_type_nm, ordr, reg_dt, rgtrid)
VALUES(dgstfn_typeid + 5, qestnrid, '124105', '수강후기' , 5, reg_dt, rgtrid)
SELECT MAX(dgstfn_typeid) dgstfn_typeid
, 141 qestnrid
, sysdate reg_dt
, '10000001' rgtrid
FROM tb_cmm_srvy_dgstfn_type
;
) 버튼을 클릭하여 작성 하시면 됩니다.