sql 批量操作（存在的更新，不存在的插入）

update A SET A.NAME=
(
  select B.NAME from #tmpTB2 B where B.ID=A.ID
)
FROM #tmpTB3 A

insert into #tmpTB3 
select * from #tmpTB2 B
where B.ID not in(select ID from #tmpTB3)      

以上為：存在的更新，不存在的插入

以下為 批量更新某個辨別字段：

UPDATE A SET A.Provider_IsActive=
(
  SELECT CASE WHEN
  (
    SELECT 1 FROM
    (
      SELECT DISTINCT Provider_System_Provider_Id 
      FROM YOUR_DB_NAME.[dbo].[YOUR_TABLE_NAME2] P 
      WHERE p.data_source = 'ika' and soft_delete_flag = 'n' and Provider_Active_Flag = 'y'
    ) B WHERE B.Provider_System_Provider_Id = left(A.External_provider_id,7)
  ) IS NULL THEN 0 ELSE 1 END AS Provider_IsActive
)
FROM YOUR_DB_NAME.[dbo].[YOUR_TABLE_NAME1] A

-- SELECT COUNT(*) FROM YOUR_DB_NAME.[dbo].[YOUR_TABLE_NAME1] A WHERE Provider_IsActive=0