SQL MERGE

declare @TempClassStu table(CTL_STNO varchar(9));

insert into @TEMPCLASSSTU(CTL_STNO)
select CS_STNO
  from REG_CLASSST
 where CS_SEMNO = 1011 
   and CS_CLASSNO = '11111';
 
--select * from @TEMPCLASSSTU;

merge COL_STUTEMP_LIST as L
using @TEMPCLASSSTU as T
on L.CTL_STNO = T.CTL_STNO
and L.CTL_SN = 100
when not matched then 
insert (CTL_SN, CTL_STNO)
values (100, T.CTL_STNO);

使用 MERGE 插入、更新，和刪除資料
MERGE (Transact-SQL)
SQL Server - MERGE Example
SQL SERVER – 2008 – Introduction to Merge Statement – One Statement for INSERT, UPDATE, DELETE

建立#TempTable與Declare @TempTable有何差別
暫存表(Temporary Tables)的使用簡介