SQL2008同Oracle的Merge用法比较

以下测试环境 SQL 2008 同Oracle 10G
SQL2008:
USE tempdb; GO IF OBJECT_ID (N'Target', N'U') IS NOT NULL DROP TABLE dbo.Target; GO Create table Target(ID int ,Name nvarchar(10)) insert into Target values(1,'a'),(3,'b'), (5,'c'),(10,'d') GO IF OBJECT_ID (N'dbo.Source', N'U') IS NOT NULL DROP TABLE dbo.Source; GO Create table Source(ID int ,Name nvarchar(10)) insert into Source values(2,'E'),(4,'F'), (6,'H'),(10,'I') /*Target--源表 ID Name 1 a 3 b 5 c 10 d */ /*Source--目标表 ID Name 2 E 4 F 6 H 10 I */ go begin tran merge Target as T using Source as S on (T.ID=S.ID) when matched and T.Name<>S.Name --ID相同，Name不同时更新 then update set T.Name=S.Name when not matched then --这里可不用写by Target(not matched by Target )没有的ID,新增 insert (ID,Name)values(S.ID,S.Name) when not matched by source then --删除Target表在Source表没有的记录 delete OUTPUT $action, inserted.ID AS SourceID, inserted.Name AS SourceName, deleted.ID AS TargetID, deleted.Name AS TargetName; select * from Target select * from Source rollback tran /*$action $action SourceID SourceName TargetID TargetName INSERT 2 E NULL NULL INSERT 4 F NULL NULL INSERT 6 H NULL NULL DELETE NULL NULL 1 a DELETE NULL NULL 3 b DELETE NULL NULL 5 c UPDATE 10 I 10 d Target ID Name 10 I 2 E 4 F 6 H Source ID Name 2 E 4 F 6 H 10 I */ go
Oracle环境:
/**删除表 begin execute immediate ' drop table Target'; exception when others then null; end; begin execute immediate ' drop table Source'; exception when others then null; end; **/ Create table Target(ID int ,Name varchar2(10)); insert into Target values(1,'a'); insert into Target values(3,'b'); insert into Target values(5,'c'); insert into Target values(10,'d'); Create table Source(ID int ,Name varchar2(10)); insert into Source values(2,'E'); insert into Source values(4,'F'); insert into Source values(6,'H'); insert into Source values(10,'I'); /**Merge Into 语句代替Insert/Update**/ MERGE INTO Target T USING Source S ON (T.ID = S.ID) WHEN MATCHED THEN UPDATE SET T.Name = S.Name WHERE T.Name<>S.Name WHEN NOT MATCHED THEN INSERT VALUES (S.ID, S.Name); /**删除不存在Source表记录**/ delete Target where not exists(select 1 from Source where ID=Target.ID);
/**--两表结果 Target/Source ID NAME 10 I 6 H 4 F 2 E **/