[MSSQL] Merge사용

declare @Test table

(

     EmployeeID nvarchar(10),

     EmployeeName nvarchar(30)

)

 

insert @Test

select '1', '김'

union all

select '209001', '홍'

 

select * from @Test

 

declare @TestInfo table

(

     RowStatus nvarchar(10),

     EmployeeID nvarchar(10),

     EmployeeName nvarchar(30)

)

 

insert @TestInfo

select 'Modified', '209001', '홍길동'

union all

select 'Added', '209002', '갑돌이'

union all

select 'Deleted', '1', '김'

 

merge @Test as E

using @TestInfo as Info on

     E.EmployeeID = Info.EmployeeID

when matched and RowStatus = 'Modified' then

     update

           set E.EmployeeName = Info.EmployeeName

when matched and RowStatus = 'Deleted' then

     delete

when not matched and RowStatus = 'Added' then  

     insert (EmployeeID, EmployeeName)

     values (Info.EmployeeID, Info.EmployeeName)

;

 

select * from @Test