데이터베이스 질문과 답변

오라클 다중 insert 0 2 2,669

by 준일정 [2023.11.03 14:55:21]

4.png (4,925Bytes)
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
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

 

?
1
2
3
4
5
6
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 일 것 같습니다.
 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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
;
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입