하우스하는 개발자

한번에 UPDATE 와 INSERT 를 구분하여 실행

테이블을 갱신할때, 해당레코드가 있으면, UPDATE, 없으면 INSERT를 실행해야 할때가 있다.

소스코드에서 IF을 사용해서 존재체크를 하고, 처리구분을 할 필요성이 있다.

SQLServer 2008부터 Merge를 사용하면 IF를 사용하지 않고, 처리구분이 가능하다.

한개의 테이블을 MERGE

UPDATE의 예

CREATE TABLE test_table( no INT, name VARCHAR(20), age INT )
INSERT INTO test_table VALUES( 10,'jkim01',40)

MERGE INTO test_table AS A
    USING (SELECT 10 AS no,'jkim03' AS name, 30 AS age ) AS B
    ON ( A.no = B.no )
    WHEN MATCHED THEN
        UPDATE SET
             name = B.name ,age = B.age
    WHEN NOT MATCHED THEN
        INSERT (no,name,age)
        VALUES (B.no,B.name,B.age)
;
SELECT no, name, age FROM test_table ORDER BY no
DROP TABLE test_table

// 결과
//no    name     age
//10    jkim03   30

검증용으로 투입했던 데이터가 「jkim03」으로 갱신되었습니다.

ON이하의 「A.no = B.no」의 조건이 일치했으므로, UPDATE(갱신) 되었습니다.

INSERT 예

CREATE TABLE test_table( no INT, name VARCHAR(20), age INT )
INSERT INTO test_table VALUES( 10,'jkim01',40)

MERGE INTO test_table AS A
    USING (SELECT 20 AS no,'jkim02' AS name, 40 AS age ) AS B
    ON ( A.no = B.no )
    WHEN MATCHED THEN
        UPDATE SET
             name = B.name ,age = B.age
    WHEN NOT MATCHED THEN
        INSERT (no,name,age)
        VALUES (B.no,B.name,B.age)
;
SELECT no, name, age FROM test_table ORDER BY no
DROP TABLE test_table

//실행결과
no    name    age
10    jkim03  30
20    jkim02  40

ON이하의 「A.no = B.no」의 조건이 일치하지 않았으므로, INSERT(투입) 되었습니다.

컴럼(column)의 추가(add)

-- 구문
ALTER TABLE [테이블명] ADD [컬럼명] [데이터명] [NULL or NOT NULL DEFAULT ××];
-- 예
ALTER TABLE user ADD NAME nvarchar(60) NOT NULL DEFAULT '';

컴럼(column)의 수정(modify)

-- 구문
ALTER TABLE [테이블명] DROP COLUMN [컬럼명];
-- 예
ALTER TABLE user DROP COLUMN NAME;

컴럼(column)의 삭제(delete)

-- 구문
ALTER TABLE [테이블명] DROP COLUMN [컬럼명];
-- 예
ALTER TABLE user DROP COLUMN NAME;

주의점

신규로 컬럼을 추가할 겨우 NOT NULL만 설정하는것은 안된다.

해결방법으로는,

일단 [ NOT NULL DEFAULT '' ]로 컬럼을 추가한후에 [ NOT NULL ]로 변경하는 하면 된다.

-- 컬럼을 추가
ALTER TABLE user ADD NAME nvarchar(6) NOT NULL DEFAULT '';
-- 레코드를 갱신
UPDATE user SET NAME ='hoge';
-- NOT NULL로 변경
ALTER TABLE user ALTER COLUMN NAME nvarchar(6) NOT NULL;