[ORACLE/SQL] INSERT ALL INTO - 여러 테이블에 동시에 데이터 넣기

INSERT ALL INTO

갤러리 게시판에 글 등록하는 쿼리를 짜는데

테이블을 잘게 쪼개놨더니 INSERT문을 여러 번 써야 했다.

구글링 해보니 INSERT ALL 구문이 있음을 발견!

 

 

INSERT 문

• INSERT INTO : 1개 테이블에 1개 행 입력
• INSERT SELECT : 테이블2에서 검색한 컬럼의 데이터들을 테이블1의 컬럼에 삽입(복붙)
• INSERT ALL INTO : 여러 테이블에 여러 행 입력, 다른 테이블에 동시에 같은 행 입력.

 

 

INSERT ALL INTO

활용법 1.

복붙하는 경우가 아닐 때는 가상테이블 DUAL을 이용한다.

INSERT ALL INTO 테이블x (컬럼ax, 컬럼bx) VALUES(값x1, 값x2) INTO 테이블y (컬럼ay, 컬럼by) VALUES(값y1, 값y2) SELECT * FROM DUAL;

 

 

활용법 2.

기존의 테이블x에서 가져와서 테이블y, 테이블z에 각각 집어넣는 경우

INSERT ALL WHEN 조건x1 THEN INTO 테이블y (컬럼y1, 컬럼y2) WHEN 조건x2 THEN INTO 테이블z (컬럼z1, 컬럼z2) SELECT 컬럼x1, 컬럼x2  FROM 테이블x;

 

활용법 3.

기존의 테이블x에서 가져와서 테이블y, 테이블z에 동시에 집어넣는 경우

INSERT ALL INTO 테이블y (컬럼y1, 컬럼y2) INTO 테이블z (컬럼z1, 컬럼z2) SELECT 컬럼x1, 컬럼x2  FROM 테이블x WHERE 조건x1;

 

출처: https://blog.naver.com/PostView.naver?blogId=regenesis90&logNo=222198156918&categoryNo=0&parentCategoryNo=0

---

활용해보았는데 에러가 발생했다.

무결성 제약 조건에 위배된다고 데이터가 들어가지 않는다.

 

알고보니 RECIPE_IMG, RECIPE_MAT 테이블들의 부모 테이블인 RECIPE 테이블에 데이터가 없는 상태라 RECIPE 테이블에 데이터 넣으면서 동시에 넣을 수가 없는 것...

어쩔 수 없이 나눠서 RECIPE 테이블에 INSERT를 먼저 해주었다.

 

그리고나서 나머지 테이블들 넣으려 하니 이번에는 에러는 안 뜨나 0개 행이 삽입되었다며 데이터가 들어가지 않았다.

커밋도 했는데!

 

그래서 WHERE 로 조건을 추가해주었더니 이번에는 또 무결성 제약 조건 위배 된다며 에러가..

찾아보니 SEQUENCE를 이렇게 동시에 쓸 수 없다고 한다.

INSERT ALL은 하나의 쿼리문으로 인식해서 NEXTVAL이 같은 값으로 들어간다.

그런데 나는 시퀀스를 넘버에 쓰고 넘버는 PK가 걸려 있었으니 에러가 나는 게 당연..

 

그래서 결국 INSERT ALL은 다음에 쓰기로 하고

쿼리 하나만 짜서 반복문 돌려서 여러 개 넣기로 했다.

 

 

정리

INSERT ALL 사용 시 주의사항

- 부모 테이블 데이터는 먼저 넣어주어야 한다. (PK 위배)

- 기존 테이블에서 가져와서 넣는 경우 WHERE 조건을 걸어줘야 한다.

- 동시에 넣을 때 SEQUENCE.NEXTVAL 을 사용할 수 없다.

 

---

전체 쿼리

INSERT ALL 시도

INSERT ALL
INTO RECIPE VALUES (RECIPE_SEQ.NEXTVAL, 1, '오늘은 내가 요리사!', '제가 만들었어요', SYSDATE, 0, 1, 1)
INTO RECIPE_IMG VALUES (RECIPE_IMG_SEQ.NEXTVAL, RECIPE_SEQ.CURRVAL, 'ee', 0)
INTO RECIPE_IMG VALUES (RECIPE_IMG_SEQ.NEXTVAL, RECIPE_SEQ.CURRVAL, 'bb', 1)
INTO RECIPE_IMG VALUES (RECIPE_IMG_SEQ.NEXTVAL, RECIPE_SEQ.CURRVAL, 'Cb', 1)
INTO RECIPE_MAT VALUES (RECIPE_MAT_SEQ.NEXTVAL, RECIPE_SEQ.CURRVAL, '짜파게티', '100')
SELECT *
FROM DUAL
;

-> 에러

-> 나눔

-- 글
INSERT INTO RECIPE
VALUES (RECIPE_SEQ.NEXTVAL, 1, '오늘은 내가 요리사!', '제가 만들었어요', SYSDATE, 0, 1, 1)
;

-- 이미지
INSERT INTO RECIPE_IMG 
VALUES (RECIPE_IMG_SEQ.NEXTVAL, 3, 'ee', 0)
;

-- 재료
INSERT INTO RECIPE_MAT
VALUES (RECIPE_MAT_SEQ.NEXTVAL, 3, '짜파게티', 100);
COMMIT;