DML) INSERT 구문 (subquery 이용, insert all 이용)

안녕하세요, 코린이의 코딩 학습기 채니 입니다.

 

개인 포스팅용으로 내용에 오류 및 잘못된 정보가 있을 수 있습니다.

---

DML (Data Manipulation Language 데이터 조작어)

- 테이블 객체의 데이터에 대해서 생성/조회/수정/삭제 (CRUD) 하는 명령어

insert (Create)

select (Read)

update (Update)

delete (Delete)

- DML 명령어 수행 시 메모리에서 우선 작업하므로 TCL (Transaction Control Language)를 통해 실제 DB에 반영(commit), 작업 취소(rollback)하는 과정이 필요

 

---

INSERT

- 테이블에 새로운 레코드를 추가하는 명령어

- 명령 성공 시마다 테이블에 행이 하나 씩 추가

- 추가할 레코드에 컬럼 값 중에 하나라도 유효하지 않은 (자료형 불일치, 제약조건 불일치...) 값이 있다면, 전체 레코드가 추가 될 수 없음

 

INSERT 구문의 문법

문법1) 테이블의 구조대로 값을 제공 / 컬럼순서, 컬럼개수가 모두 일치
insert into
	테이블명
values(컬럼값1, 컬럼값2, ....)

문법2) 컬럼명명시 : 컬럼 순서 변경, 컬럼 값을 생략하고 레코드 추가 가능
insert into
	테이블명(컬럼1, 컬럼2, ....)
values(컬럼값1, 컬럼값2, ....)

 

먼저 하나의 sample_1 테이블을 생성하였습니다.

create table sample_1(
    a number,
    b varchar2(20) default '안녕',    -- 기본값을 '안녕'으로 설정
    c varchar2(20) not null,            -- 필수 입력해야되는 컬럼
    d date default sysdate not null -- 기본값을 sysdate, 필수 입력해야되는 컬럼 지정
);

select * from sample_1;
desc sample;

각 컬럼의 자료형, 제약조건은 위와 같습니다.

그럼 문법1, 2를 이용하여 데이터를 추가해보도록 하겠습니다.

 

문법 1) 데이터 추가

-- 문법1)
insert into sample_1 values (1, 'hello', 'abcdefg', '1999/09/09');
insert into sample_1 values (null, default, '가나다', '2022/04/22');
select * from sample_1;

데이터가 추가 된 것을 알 수 있으며, not null이 아닌 컬럼에는 null을 이용하여 null 값을 입력 / default 값인 '안녕'을 입력하기 위해 b 컬럼에 default를 입력해주었습니다.

 

insert into sample_1 values (2, 'hi', 'abc', '1999/01/01', 10000);  --ORA-00913: too many values
insert into sample_1 values (3, 'ho', 'def');   --ORA-00947: not enough values
insert into sample_1 values (null, default, null, default); 
--ORA-01400: cannot insert NULL into ("KH"."SAMPLE_1"."C")

이번에는 지정 되어 있는 컬럼 수(4개)보다 많게, 혹은 적게, not null인 c컬럼에 null 값을 추가해보았습니다.

각각

"ORA-00913: too many values"

"ORA-00947: not enough values"

"ORA-01400: cannot insert NULL into ("KH"."SAMPLE_1"."C")"

와 같은 오류 메세지가 출력 되는 것을 확인할 수 있습니다.

또한, 컬럼 값 중 하나라도 불일치 하는 레코드가 있으면 전체 레코드가 추가될 수 없다는 것을 확인할 수 있습니다.

 

 

문법 2) 데이터 추가

- null 컬럼만 생략 가능 (default 값이 있다면, 자동으로 기본 값 처리)

- not null 컬럼도 default 값이 지정되어 있는 경우 생략 가능

-- 문법2)
insert into sample_1(c, d) values ('하이', to_date('2022-03-01'));
insert into sample_1(a, b, c) values (2, '여보세요', 'hello');
select * from sample_1;

3행의 경우 c, d 컬럼에 '하이'와 '2022-03-01'을 입력하였지만, b의 default 값이 '안녕'이기 때문에 '안녕'도 같이 추가 되었습니다.

4행의 경우 a, b, c, 컬럼에 2, '여보세요', 'hello'를 입력하였지만, d의 dfault값이 sysdate이므로 오늘 날짜가 입력 되었습니다.

null 컬럼, not null 컬럼 모두 default값이 지정 되어 있는 경우 생략 가능하며, 자동으로 기본 값 처리가 됩니다.

 

insert into sample_1(a, b) values (3, '여보세요');
-- ORA-01400: cannot insert NULL into ("KH"."SAMPLE_1"."C")
insert into sample_1(a, b, c) values (3, '안녕잘가안녕잘가안녕잘가', 'hello');
-- ORA-12899: value too large for column "KH"."SAMPLE_1"."B" (actual: 36, maximum: 20)

이번에는 not null 값인 c컬럼을 생략 및 지정 자리수를 초과해보았습니다.

각각

"ORA-01400: cannot insert NULL into ("KH"."SAMPLE_1"."C")"

"ORA-12899: value too large for column "KH"."SAMPLE_1"."B" (actual: 36, maximum: 20)"

오류 메세지가 출력되는 것을 확인할 수 있습니다.

 

이번에는 연습용 employee_ex2 테이블을 생성하여 데이터를 추가해보겠습니다.

-- 연습용 employee_ex2 생성
create table employee_ex2
as
select * from employee;

select * from employee_ex2;
desc employee_ex2;

alter table employee_ex2
add constraint pk_employee_ex2 primary key(emp_id)   -- 기본키(식별자)
add constraint uq_employee_ex2_emp_no unique(emp_no) -- 유일키(중복허용X)
add constraint fk_employee_ex2_dept_code foreign key(dept_code) references department(dept_id)   -- 외래키
add constraint fk_employee_ex2_job_code foreign key(job_code) references job(job_code)   -- 외래키
--add constraint fk_employee_ex_manager_id foreign key(manager_id) references employee_ex(emp_id)   -- 외래키
add constraint ck_employee_ex2_quit_yn check(quit_yn in ('Y', 'N'))  -- 체크키
modify quit_yn default 'N'  -- 기본값 설정
modify hire_date default sysdate;

 

사번: 301
이름: 함지민
주민번호: 781020-2123453
이메일: hamham@kh.or.kr
전화번호: 01012343334
부서코드: D1
직급코드: J4'
급여등급: S3
급여: 4300000
보너스: 0.2
관리자: 200 정보 입력하기

insert into 
	employee_ex2
values (
    '301', '함지민', '781020-2123453', 'hamham@kh.or.kr', '01012343334', 'D1', 
    'J4', 'S3', 4300000, 0.2, '200', default, null, default
);

 

사번: 302
이름: 장채현
주민번호: 901123-1080503
이메일: jang_ch@kh.or.kr
전화번호: 01033334444
부서코드: D2
직급코드: J7
급여등급: S3
급여: 3500000
보너스: 없음
관리자: 201 정보 입력하기

insert into employee_ex2 
	(emp_id, emp_name, emp_no, email, phone, dept_code, job_code, sal_level, salary, manager_id) 
values (
        '302', '장채현', '901123-1080503', 'jang_ch@kh.or.kr', '01033334444', 'D2', 
        'J7', 'S3', 3500000, '201'
);

 

not null 값만 입력해보기

insert into 
    employee_ex2 (emp_id, emp_name, emp_no, job_code, sal_level)
values (
    '303', '박채니', '990119-1234567', 'J1', 'S1'
);
select * from employee_ex2;

데이터들이 모두 잘 추가 된 것을 확인할 수 있습니다.

 

---

Data Migration 시에 유용한 insert 구문

① subquery를 사용한 insert

create table emp_info2 (
    emp_id varchar2(3),
    emp_name varchar2(50),
    dept_title varchar2(50),
    job_name varchar2(30)
);
select * from emp_info2;

insert into emp_info2 (
    select
        emp_id,
        emp_name,
        (select dept_title from department where dept_id = e.dept_code),
        (select job_name from job where job_code = e.job_code)
    from
        employee e
);

 

② insert all

- 2개 이상의 테이블에 나눠서 insert 처리

→ emp_hire_date2

→ emp_manager2

 

emp_hire_date2 테이블 생성

create table emp_hire_date2
as
select
    emp_id, emp_name, hire_date
from
    employee
where
    1 = 0;  -- 레코드는 제외하고, 테이블 구조(컬럼순서, 자료형)만 복제
    
desc emp_hire_date2;
select * from emp_hire_date2;

where 1 = 0; 이기 때문에 레코드는 제외하고, 테이블 구조 (컬럼순서, 자료형)만 복제 해왔습니다.

 

emp_manager2 테이블 생성

create table emp_manager2
as
select
    emp_id,
    emp_name,
    manager_id,
    emp_name as manager_name    -- 컬럼 타입만 지정해줌 (타입은 emp_name, 컬럼명은 manager_name(별칭))
from
    employee
where
    1 = 0;

desc emp_manager2;
select * from emp_manager2;

emp_manager2 테이블도 마찬가지로 생성하였으며, manager_name이라는 컬럼이 employee테이블에 없기 때문에 컬럼 타입 지정을 위해 emp_name으로 생성하였습니다.

다만, manager_name은 nullable해야하는데 emp_name을 그대로 가져와서 not null 컬럼이 되었습니다.

nullable한 컬럼으로 변경해주겠습니다.

alter table emp_manager2 modify manager_name null;

 

데이터 추가 (insert all)

insert all
into emp_hire_date2 values (emp_id, emp_name, hire_date)
into emp_manager2 values (emp_id, emp_name, manager_id, manager_name)
select 
    emp_id,
    emp_name,
    manager_id,
    (select emp_name from employee where emp_id = e.manager_id) as manager_name,
    hire_date
from
    employee e;
commit;

emp_hire_date2

emp_manager2