Chap03-2. 데이터 변경을 위한 SQL 문 - INSERT, UPDATE, DELETE, MERGE

Chap03-2. 데이터 변경을 위한 SQL 문

이전 포스팅 Chap03-1. Transact-SQL 기본 - SELECT 에서는 T-SQL에서 데이터를 조회할 수 있는 SELECT문에 대해 알아보았다. 이번 포스팅에서는 데이터를 변경할 수 있는 INSERT/UPDATE/DELETE 등에 대해 알아보도록 하자.

1. 데이터의 삽입 : INSERT

1.1 INSERT 문 기본

INSERT문은 테이블에 데이터를 삽입하는 명령어이며, 기본적인 형식은 아래와 같다.

INSERT [INTO] <테이블> [(컬럼1, 컬럼2, ...)] VALUES (값1, 값2, ...)

다음의 예제는 sqlDB에 testTbl1을 만든다음 INSERT를 이용해 데이터를 삽입하는 쿼리문이다.

use sqlDB;
CREATE TABLE testTbl1 (id int, userName nchar(3), age int);
GO
INSERT INTO testTbl1 (id, userName, age) VALUES (1, '홍길동', 25);

INSERT 문은 아래와 같이 테이블 이름 다음에 나오는 컬럼은 생략 가능하다. 하지만, 생략할 경우 VALUES다음에 나오는 값들의 순서 및 개수가 테이블에 정의된 컬럼의 순서 그리고 개수와 같아야 한다.

INSERT INTO testTbl1 VALUES (2, '임꺽정', 30);

만약 특정 컬럼의 값만 입력하고 싶을 경우, 아래와 같이 입력할 컬럼과 VALUES에 컬럼에 해당하는 데이터를 입력하면 된다. 입력하지 않은 컬럼은 NULL값이 들어가게 된다.

INSERT INTO testTbl1 (id, userName) VALUES (3, '한가인');

1.2 자동으로 증가하는 IDENTITY

테이블의 컬럼(속성)이 IDENTITY로 지정되어 있을 경우, 이 컬럼은 INSERT를 해줄 때 자동으로 값이 입력되기 때문에 이 컬럼은 없는 컬럼이라고 생각하면 된다. IDENTITY는 데이터 입력 시 자동으로 1 부터 증가하는 값을 입력해 주는 역할을 한다. 또한, CREATE TABLE 에서 DEFAULT로 입력할 데이터를 설정 해놓으면, 데이터를 INSERT할 때 자동으로 DEFAULT 값이 입력된다.

아래의 예제는 testTbl2 테이블에 id 컬럼에 IDENTITY로 설정하고, nation컬럼에 DEFAULT로 대한민국으로 설정한 뒤 데이터를 INSERT하는 쿼리문이다.

USE sqlDB;
CREATE TABLE testTbl2
    (id int IDENTITY,
     userName nchar(3),
     age int,
     nation nchar(4) DEFAULT '대한민국');
GO
-- 데이터 입력
INSERT INTO testTbl2 VALUES ('강동원', 27, DEFAULT);
GO
-- 데이터 조회
SELECT * FROM testTbl2;

SET IDENTITY_INSERT

만약, 강제로 IDENTITY의 값을 직접 입력하고 싶을 경우 아래와 같이 SET IDENTITY_INSERT 문을 이용해 IDENTITY값을 직접 입력할 수 있다.

-- IDENTITY 직접 입력 설정 ON
SET IDENTITY_INSERT testTbl2 ON;
GO
-- 데이터 입력
INSERT INTO testTbl2 (id, userName, age, nation)
    VALUES (11, '키아누', 31, '미국');
​
-- 데이터 조회
SELECT * FROM testTbl2;

이때 주의해야할 사항은 위의 쿼리문처럼 IDENTITY_INSERT를 ON으로 변경했을 경우, 데이터를 입력할 때 입력할 컬럼을 명시해줘야 한다.

먼저, 오류가 나는 쿼리문 부터 살펴보자. 아래의 쿼리와 같이 입력할 데이터의 컬럼을 명시해주지 않을 경우에 다음과 같은 에러가 나타난다.

-- 오류나는 쿼리문
INSERT INTO testTbl2 VALUES (12, '장만옥', 35, '중국');

메시지 8101, 수준 16, 상태 1, 줄 2
테이블 'testTbl2'에 있는 ID 열의 명시적 값은 열 목록이 사용되고 IDENTITY_INSERT가 ON일 때만 지정할 수 있습니다.

그렇다면, 이번에는 입력할 데이터의 컬럼을 명시해줄 경우를 살펴보면, 아래와 같이 입력이 되는것을 확인할 수 있다.

-- 오류가 나지 않는 쿼리문
INSERT INTO testTbl2 (id, userName, age, nation)
    VALUES (12, '장만옥', 35, '중국');
-- 데이터 조회
SELECT * FROM testTbl2;

위의 쿼리는 SET IDENTITY_INERT가 ON으로 설정되어 있었다. 이를 OFF로 변경하고 데이터를 입력해주게 되면 id값은 최대값+1부터 다시 자동으로 입력이 된다.

-- IDENTITY 직접 입력 설정OFF
SET IDENTITY_INSERT testTbl2 OFF;
INSERT INTO testTbl2 VALUES ('다꾸앙', 23, '일본');
SELECT * FROM testTbl2;

현재 IDENTITY 값 확인하기

특정 테이블에 설정된 IDENTITY값을 확인하려면 아래의 형식을 통해 확인할 수 있다.

SELECT IDENT_CURRENT('테이블이름');
또는
SELECT @@IDENTITY;

IDENT_CURRENT는 특정 테이블을 지정하여 그 테이블에 설정된 IDENTITY값을 확인할 수 있고, @@IDENTITY는 현재의 쿼리창에서 가장 최근에 생성된 IDENTITY값을 확인할 수 있다.

SELECT IDENT_CURRENT('testTbl2');

1.3 SEQUENCE

SEQUENCE는 IDENTITY와 같은 역할을 하는 쿼리문으로 SQL Server 2012부터 제공된 기능이다. SEQUENCE를 이용하기 위해 1.2에서 만들었던 testTbl2와 같은 testTbl3을 만든다음 SEQUENCE를 적용해보자. 아래의 쿼리처럼 testTbl3에는 IDENTITY를 사용하지 않는다.

-- testTbl3 생성
USE sqlDB;
CREATE TABLE testTbl3
    (id int,
     userName nchar(3),
     age int,
     nation nchar(4) DEFAULT '대한민국');
GO

시퀀스 생성하기

위의 쿼리를 통해 testTbl3 테이블을 만든 다음, 시퀀스(SEQUENCE)를 생성해보자. 아래의 쿼리처럼 시작값은 1로, 증가값도 1로 설정한다.

CREATE SEQUENCE idSEQ  -- 이름이 idSEQ인 시퀀스
    START WITH 1  -- 시작값
    INCREMENT BY 1 ;  -- 증가값
GO

위와 같이 idSEQ라는 시퀀스를 만든 다음, 데이터를 INSERT할 때 NEXT VALUE FOR 시퀀스이름을 사용하면 시퀀스가 적용된다.

-- 데이터 입력
INSERT INTO testTbl3 VALUES 
    (NEXT VALUE FOR idSEQ, '강동원', 27, DEFAULT);
-- 데이터 조회
SELECT * FROM testTbl3;

testTbl3테이블을 생성할 때, id 컬럼에는 어떠한 값을 지정 해주지 않았기 때문에 id값을 변경하여 입력하고 싶을 경우에는 그냥 아래와 같이 INSERT하면 된다.

-- 데이터 입력
INSERT INTO testTbl3 VALUES 
    (11, '키아누', 31, '미국');
-- 데이터 조회
SELECT * FROM testTbl3;

시퀀스 값 설정하기

강제로 시퀀스(SEQUENCE)의 시작값을 설정하고 싶을 경우 ALTER을 이용하여 변경할 수 있다.

-- 시퀀스 시작값 변경
ALTER SEQUENCE idSEQ
    RESTART WITH 12;  -- 시작값 다시 설정
GO
-- 데이터 입력
INSERT INTO testTbl3 VALUES 
    (NEXT VALUE FOR idSEQ,'다꾸앙', 23, '일본');
GO
-- 데이터 조회
SELECT * FROM testTbl3;

시퀀스 CYCLE 설정

시퀀스를 이용해 특정 범위의 값이 계속 반복해서 입력되도록 설정할 수 있다. 아래의 예제는 testTbl4테이블을 생성한 다음, 100, 200, 300의 값이 반복되어 입력되게끔 시퀀스를 설정한 예제이다.

-- 테이블 생성
CREATE TABLE testTbl4 (id INT);
GO
-- 시퀀스 생성
CREATE SEQUENCE cycleSEQ
    START WITH 100
    INCREMENT BY 100
    MINVALUE 100  -- 최소값
    MAXVALUE 300  -- 최대값
    CYCLE;  -- 반복설정
GO
-- 데이터 입력
INSERT INTO testTbl4 VALUES (NEXT VALUE FOR cycleSEQ);
INSERT INTO testTbl4 VALUES (NEXT VALUE FOR cycleSEQ);
INSERT INTO testTbl4 VALUES (NEXT VALUE FOR cycleSEQ);
INSERT INTO testTbl4 VALUES (NEXT VALUE FOR cycleSEQ);
GO
-- 데이터 조회
SELECT * FROM testTbl4;

시퀀스를 DEFAULT와 함께 사용하기

시퀀스를 DEFAULT와 함께 사용하면 1.2의 IDENTITY처럼 데이터 INSERT 할 때, 생략해도 자동으로 값이 입력, 증가되도록 설정할 수 있다. 아래의 예제는 testTbl5 테이블에 id 컬럼을 시퀀스와 DEFAULT를 사용한 쿼리이다.

-- 시퀀스 생성
CREATE SEQUENCE autoSEQ
    START WITH 1
    INCREMENT BY 1;
GO
-- 테이블 생성
CREATE TABLE testTbl5
    (id int DEFAULT (NEXT VALUE FOR autoSeq),  -- id 컬럼에 시퀀스 지정
     userName nchar(3));
GO
-- 데이터 입력
INSERT INTO testTbl5(userName) VALUES ('강동원');
INSERT INTO testTbl5(userName) VALUES ('키아누');
INSERT INTO testTbl5(userName) VALUES ('다꾸앙');
GO
-- 데이터 조회
SELECT * FROM testTbl5;

1.4 대량의 샘플데이터 생성

지금까지는 데이터를 입력하기 위해서는 INSERT INTO ... VALUES를 통해 각각의 데이터를 입력하는 방법에 대해 살펴보았다. 만약 다른 테이블에서 여러건의 샘플데이터를 입력해야할 경우 위와 같은 방법은 시간이 오래 걸릴 것이다. 이 때, INSERT INTO ... SELECT 구문을 이용할 수 있으며, SELECT문의 컬럼의 개수와 INSERT 문의 컬럼의 개수는 같아야 한다.

INSERT INTO 테이블이름 (컬럼이름1, 컬럼이름2, ...)
    SELECT 문 ;

아래의 예제는 AdentureWorks2014 DB(여기 참고)의 Person 테이블에서의 특정 컬럼들의 샘플데이터를 testTbl6 테이블에 입력하는 쿼리문이다.

-- 테이블 생성
CREATE TABLE testTbl6 
    (id int, Fname nvarchar(50), Lname nvarchar(50));
GO
-- 데이터 입력
INSERT INTO testTbl6
    SELECT BusinessEntityID, FirstName, LastName
        FROM AdventureWorks2014.Person.Person;
GO
-- 데이터 조회
SELECT TOP(5) * FROM testTbl6;

2. 데이터의 수정 : UPDATE

기존에 입력되어 있는 데이터를 변경하려면 다음과 같이 UPDATE문을 사용하면 된다.

UPDATE 테이블이름
SET 컬럼1=값1, 컬럼2=값2, ...
WHERE 조건;

WHERE절을 이용해 변경하고자 하는 데이터를 찾은 다음 UPDATE 해준다. UPDATE문에서 WHERE절은 생략할 수 있지만, 생략하게 되면 전체 데이터가 변경되므로, 전체 데이터를 변경하는 경우를 제외하고는 WHERE절을 써줘야 한다.

아래의 예제는 1.4에서 만든 testTbl6에서 Fname='Kim'인 데이터의 Lname 을 '없음'으로 변경하는 예제이다.

-- 데이터 변경
UPDATE testTbl6
    SET Lname = '없음'
    WHERE Fname = 'Kim';
GO
-- 데이터 조회
SELECT * FROM testTbl6
WHERE Fname = 'Kim';

3. 데이터의 삭제 : DELETE

DELETE는 데이터를 행(로우) 단위로 삭제하며, 형식은 다음과 같다.

DELETE 테이블이름 WHERE 조건;

UPDATE와 마찬가지로 WHERE조건을 생략하면, 전체 데이터가 삭제된다. 아래의 예제는 testTbl6에서 Fname='Kim'인 데이터를 삭제하는 예제이다.

-- 데이터 삭제
DELETE FROM testTbl6 WHERE Fname = 'Kim';
GO
-- 데이터 조회
SELECT COUNT(*) AS [데이터개수] 
    FROM testTbl6
    WHERE Fname = 'Kim';

3.1 DELETE vs. DROP vs. TRUNCATE

만약, 대용량의 데이터를 가지고 있는 테이블을 삭제해야 할 때에는 DELTE/DROP/TRUNCATE를 사용해서 삭제할 수 있다. 하지만, 이들 중에서 어떤것을 사용하는 것이 좋은가에 대해서는 다음의 예제를 통해 살펴보도록 하자.

3.1.1. 대용량의 테이블 생성

먼저, DELETE/DROP/TRUNCATE를 각각 비교하기 위해 AdventureWorks2014 DB에서 약 12만건이 있는 테이블을 복사해서 bigTbl1, bigTbl2, bigTbl3 테이블을 만들어 준다.

USE sqlDB;
SELECT * INTO bigTbl1 FROM AdventureWorks2014.Sales.SalesOrderDetail;
SELECT * INTO bigTbl2 FROM AdventureWorks2014.Sales.SalesOrderDetail;
SELECT * INTO bigTbl3 FROM AdventureWorks2014.Sales.SalesOrderDetail;
GO

3.1.2 SQL Server Profiler

SSMS(SQL Server Managment Studio) 메뉴에서 [도구] → [SQL Server Profiler]를 실행한 다음, 적절한 이름을 입력한 뒤에 아래의 그림과 같이 '템플릿 사용'에서 'TSQL_Duration'을 선택한 다음 <실행> 버튼을 클릭한다.

3.1.3 DELETE/DROP/TRUNCATE 비교

아래와 같이 DELETE/DROP/TRUNCATE문을 이용해 테이블의 데이터를 삭제한다. DROP의 경우에는 테이블 자체를 삭제한다.

-- DELETE
DELETE FROM bigTbl1;
GO
-- DROP
DROP TABLE bigTbl2;
GO
-- TRUNCATE
TRUNCATE TABLE bigTbl3;
GO

위의 쿼리를 실행한 후, 3.1.2에서 생성한 Profiler를 통해 비교해보면 다음과 같다.

DML 문인 DELETE는 트랜잭션 로그를 기록하기 때문에 데이터를 삭제하는데 가장 오래 걸렸다. DDL 문인 DROP은 테이블 자체를 삭제하며, DDL은 트랜잭션을 발생시키지 않기 때문에 속도가 빠르며, TRUNCATE또한 DDL이며 DELETE와 같은 역할을 하지만 트랜잭션 로그를 기록하지 않기 때문에 속도가 빠르다. 따라서, 대용량 데이터를 가진 테이블을 삭제할 때에는, 테이블 자체가 필요없을 경우 DROP을 사용하고, 테이블이 필요할 경우에는 TRUNCATE를 사용하는 것이 좋다.

4. 조건부 데이터 변경 : MERGE

MERGE문은 SQL Server 2008 부터 제공되며, 원본 테이블과의 조인 결과를 기반으로 대상 테이블에서 삽입(INSERT), 업데이트(UPDATE) 또는 삭제(DELETE) 작업을 수행한다. 예를 들어 원본 테이블과의 차이점에 따라 대상 테이블에서 행을 삽입, 업데이트 및 삭제하여 두 테이블을 동기화할 수 있다(참고: docs.microsoft.com). MERGE문의 형식은 다음과 같다.

[ WITH <common_table_expression> [,...n] ]  
MERGE   
    [ TOP ( expression ) [ PERCENT ] ]   
    [ INTO ] <target_table> [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ]  
    USING <table_source>   
    ON <merge_search_condition>  
    [ WHEN MATCHED [ AND <clause_search_condition> ]  
        THEN <merge_matched> ] [ ...n ]  
    [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]  
        THEN <merge_not_matched> ]  
    [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]  
        THEN <merge_matched> ] [ ...n ]  
    [ <output_clause> ]  
    [ OPTION ( <query_hint> [ ,...n ] ) ]      
;  

MERGE문을 이용하기 위해 다음과 같은 예시를 통해 알아보도록 하자.

4.1 시나리오

예를 들어, 멤버 테이블(memberTBL)에는 기존 회원들이 있는데, 이 memberTBL에 직접 INSERT, DELETE, UPDATE를 사용하면 안되며, 회원의 가입, 변경 탈퇴가 생기면 변경 테이블(changeTBL)에 INSERT문으로 회원의 변경사항을 입력한다. 변경 테이블의 변경 사항은 '신규가입/주소변경/회원탈퇴' 3가지라고 가정한다.

4.2 memberTBL 및 changeTBL 만들기

Chap03-1. Transact-SQL 기본 - SELECT 에서 생성한 userTbl을 이용해 memberTBL로 복사하여 memberTBL을 생성한다.

USE sqlDB;
-- 테이블 복사
SELECT userID, name, addr INTO memberTBL FROM userTbl;
-- 테이블 조회
SELECT *FROM memberTBL

그런다음 changeTBL을 아래와 같이 만들어 주고, 1명의 신규가입과 2명의 주소변경, 2명의 회원탈퇴가 있었다고 가정하자.

-- 테이블 생성
CREATE TABLE changeTBL
    (changeType NCHAR(4),  -- 변경 사유
     userID char(8),
     name nvarchar(10),
     addr nchar(2));
GO
-- 데이터 삽입
INSERT INTO changeTBL VALUES
    ('신규가입', 'TPN', '티파니', '미국'),
    ('주소변경', 'LSG', null, '제주'),
    ('주소변경', 'LJB', null, '영국'),
    ('회원탈퇴', 'BBK', null, null),
    ('회원탈퇴', 'SSK', null, null);
GO
-- 데이터 조회
SELECT * FROM changeTBL;

4.3 MERGE를 이용해 memberTBL 데이터 변경

마지막으로 MERGE문을 이용해 changeTBL에서 changeType(변경사유) 컬럼을 이용해 기존 멤버 테이블인 memberTBL의 데이터를 변경해 준다.

-- MERGE
MERGE memberTBL AS M  -- 변경될 테이블 (target 테이블)
    USING changeTBL AS C  -- 변경할 기준이 되는 테이블 (source 테이블)
    ON M.userID = C.userID  -- userID를 기준으로 두 테이블을 비교
    -- 1) target 테이블에 source 테이블의 행이 없고, 사유가 '신규가입'이라면
    --      새로운 행을 추가한다.
    WHEN NOT MATCHED AND changeType = '신규가입' THEN
        INSERT (userID, name, addr) VALUES (C.userID, C.name, C.addr)
    -- 2) target 테이블에 source 테이블의 행이 있고, 사유가 '주소변경'이라면
    --      주소를 변경한다.
    WHEN MATCHED AND changeType = '주소변경' THEN
        UPDATE SET M.addr = C.addr
    -- 3) target 테이블에 source 테이블의 행이 있고, 사유가 '회원탈퇴'라면
    --      해당 행을 삭제한다.
    WHEN MATCHED AND changeType = '회원탈퇴' THEN
        DELETE;
​
-- memberTBL 조회
SELECT * FROM memberTBL;

5. 마무리

이번 포스팅에서는 데이터의 변경을 위한 T-SQL의 INSERT/UPDATE/DELETE/MERGE에 대해 알아보았다. 다음 포스팅에서는 고급 T-SQL에 대해 알아보도록 하자.