Merge 문을 통해 단일 테이블 Insert/Update/Delete 프로시저 생성

https://learn.microsoft.com/ko-kr/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver16

MERGE(Transact-SQL) - SQL Server

 

Merge 구문은 SQL Server 2008 부터 지원하며 ,  하나의 테이블에 대해 작업을 할 수 있다.

프로젝트를 다니다 보면 .NET 개발의 경우 대부분 프로시저를 많이 사용한다.
그렇기에 프로시저 구문을 보면 MyProcedure_IU 이런식으로 접미사를 붙여서 관리하는 케이스를 보게 되는데,
대상의 등록, 수정, 삭제 테이블이 1개라면 단순하게 Megre를 사용하는것도 방법이다.

1. 기본 Merge 사용법

MERGE INTO TargetTable AS target
USING SourceTable AS source
ON target.KeyColumn = source.KeyColumn
WHEN MATCHED THEN
    UPDATE 
    SET target.Column1 = source.Column1,
        target.Column2 = source.Column2    
WHEN NOT MATCHED THEN
    INSERT (Column1, Column2, KeyColumn)
    VALUES (source.Column1, source.Column2, source.KeyColumn)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE; -- 소스 테이블에 없는 행을 타겟 테이블에서 삭제합니다.

TargetTable: 데이터를 수정할 대상 테이블입니다.
SourceTable: 대상과 비교할 소스 테이블입니다.
KeyColumn: 두 테이블 간 매칭을 위한 주요 키입니다.
WHEN MATCHED: 소스와 대상이 일치할 때 수행할 작업을 정의합니다.
WHEN NOT MATCHED: 대상에 존재하지 않는 소스 데이터를 삽입합니다.
WHEN NOT MATCHED BY SOURCE: 대상에만 존재하는 데이터를 삭제합니다.


위 예문을 보면 기본적으로 테이블을 비교하여 작업하기 위함을 목적으로 만들어졌지만
이를 프로시저의 매개변수로 활용하면 다음과 같이 제어가 가능하다.

-- 예제 테이블
CREATE TABLE Dog
(
	DogId		int IDENTITY(1,1),
	Name		varchar(30),
	Address		varchar(100),
	Zipcode		varchar(10),
	Tel			varchar(20),
	CreateDate	smalldatetime
)

-- 예제 프로시저
CREATE PROCEDURE Dog_Merge
	@MergeFlag		char(1),
	@DogId			int				= 0,
	@Name			varchar(30)		= '',	
	@Address		varchar(100)	= '',	
	@Zipcode		varchar(10)		= '',	
	@Tel			varchar(20)		= ''		
AS
BEGIN

    SET NOCOUNT ON    
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

	BEGIN TRY

		MERGE Dog AS a
		USING (SELECT 
				@DogId		 AS DogID
				,@Name		 AS Name				
				,@Address	 AS Address
				,@Zipcode	 AS Zipcode
				,@Tel		 AS Tel) AS b
		ON a.DogID = b.DogID
		WHEN MATCHED AND @MergeFlag = 'U' THEN
			-- UPDATE (DogID 끼리 키가 일치하며 Flag가 'U')
			UPDATE
			SET	Name = @Name
				, Address = @Address
				, ZipCode = @Zipcode
				, Tel	  = @Tel
				, CreateDate	= GETDATE()
		WHEN MATCHED AND @MergeFlag = 'D' THEN
			-- DELETE
			DELETE
		WHEN NOT MATCHED AND @MergeFlag = 'I' THEN
			-- 등록
			INSERT  
				(Name
				, Address
				, ZipCode
				, Tel
				, CreateDate)
			VALUES 
				(@Name
				, @Address
				, @ZipCode
				, @Tel
				, GETDATE());


		SELECT 'Y' Result;


	END TRY
	BEGIN CATCH

		SELECT 'N' Result;

	END CATCH

END

 

- 프로시저는 반드시 @MergeFlag = I,U,D 를 받아야 하며 @DogId 를 키로 받아야 정상적으로 작동이 가능하다
- USING 구문에서 프로시저의 매개변수와 원본 테이블을 비교하여 데이터를 제어한다

 

1) INSERT 
 - MergeFlag 가 'I' 며 원본테이블과 DogID의 매치여부를 무시한다.
 - 나머지 다른 매개변수들을 등록한다
 - 등록된 데이터들은 IDENTITY 자동증가에 의해 DogID 가 1,2 번으로 자동 생성된다.

EXECUTE Dog_Merge @MergeFlag = 'I', @DogID = null, @Name = '김도그', @Address = '서울 강남구 다리밑', @ZipCode = '111' , @Tel = '01012341234' 
EXECUTE Dog_Merge @MergeFlag = 'I', @DogID = null, @Name = '김개', @Address = '경기도 성남시 대왕판교로 다리밑', @ZipCode = '111' , @Tel = '01012341234'

2) UPDATE
 -  MergeFlag 가 'I' 며 원본테이블과 DogID의 매치여부가 필수다
 -  위 INSERT 예시에서 2번째 등록된 데이터가 김개 인데.. 이 데이터가 김들개로 바뀐다.

EXECUTE Dog_Merge @MergeFlag = 'U', @DogID = 2, @Name = '김들개', @Address = '서울 강남구 다리밑', @ZipCode = '111' , @Tel = '01012341234'

 

3) DELETE
 -  MergeFlag 가 'U' 며 원본테이블과 DogID의 매치여부가 필수다
 - 조금전 수정했던 DogID2 번 김들개 가 삭제 된다.

EXECUTE Dog_Merge @MergeFlag = 'D', @DogID = 2