다중 Insert

- 다중 insert
SQL> INSERT ALL
INTO sal_history VALUES(empid,hiredate,sal)
INTO mgr_history VALUES(empid,mgr,sysdate)
SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR
FROM employee WHERE employee_id>200;

  

  

INSERT ALL
INTO sales_info VALUES(employee_id,week_id,sales_MON)
INTO sales_info VALUES(employee_id,week_id,sales_TUE)
INTO sales_info VALUES(employee_id,week_id,sales_WED)
INTO sales_info VALUES(employee_id,week_id,sales_THUR)
INTO sales_info VALUES(employee_id,week_id,sales_FRI)

  

SELECT employee_id,week_id,sales_MON,sales_TUE,sales_WED,sales_THUR,sales_FRI
FROM sales_source_data;

INSERT ALL
WHEN sal>10000 THEN
INTO sal_history VALUES(empid,hiredate,sal)
WHEN mgr>200 THEN
INTO mgr_history VALUES(empid,mgr,sysdate)
SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR
FROM employee WHERE employee_id>200;

  

INSERT FIRST
INTO special_sal VALUES(deptid,sal)
WHEN hiredate like ('%00%') THEN
INTO hiredate_history_00 VALUES(deptid,hiredate)
WHEN hiredate like ('%99%') THEN
INTO hiredate_history_99 VALUES(deptid,hiredate)
ELSE
INTO hiredate_history VALUES(deptid,hiredate)
SELECT department_id DEPTID, sum(salary) SAL, max(hire_date) HIREDATE
FROM employees GROUP BY department_id;

  

  

INSERT INTO mbfs_common
select invo_dt,bank_card_nam from mbfs_common
where invo_dt='20070505' and acnt_num='2016951106';

  

  

- 공백문자제거하고 insert하기
INSERT INTO dummy as SELECT rtrim(char_cod) FROM original_table;

- insert시 주의
  - '(싱글 쿼테이션)
     insert into test(deptno,dname) values(50,'what's up');
     ("단일 인용부를 지정해 주십시오" 라는 에러메시지출력)
  - 오라클에서 varchar2(4000)에 데이터 insert하기
    오라클에서 varchar2(4000)으로 타입을 정하고 PreparedStatement로 insert를

    하면 1600자도 안들어간다
    pstmt.setCharacterStream(1,contentreader,content.getBytes().length);
    이렇게 하면 long type입력하면 4000 byte 다 쓸수있다