SQL2008同Oracle的Merge用法比较

ecahkkqtef

　　以下测试环境 SQL 2008 同Oracle 10G
SQL2008:
USE tempdb;GOIF OBJECT_ID (N'Target', N'U') IS NOT NULL DROP TABLE dbo.Target;GOCreate table Target(ID int ,Name nvarchar(10))insert into Targetvalues(1,'a'),(3,'b'),(5,'c'),(10,'d')GOIF OBJECT_ID (N'dbo.Source', N'U') IS NOT NULL DROP TABLE dbo.Source;GOCreate table Source(ID int ,Name nvarchar(10))insert into Sourcevalues(2,'E'),(4,'F'),(6,'H'),(10,'I')/*Target--源表IDName1a3b5c10d*//*Source--目标表IDName2E4F6H10I*/gobegin tranmerge Target as Tusing Source as Son (T.ID=S.ID)when matched and T.Name<>S.Name--ID相同，Name不同时更新then update set T.Name=S.Namewhen 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表没有的记录deleteOUTPUT $action, inserted.ID AS SourceID, inserted.Name AS SourceName, deleted.ID AS TargetID, deleted.Name AS TargetName;select * from Targetselect * from Sourcerollback tran/*$action$actionSourceIDSourceNameTargetIDTargetNameINSERT2ENULLNULLINSERT4FNULLNULLINSERT6HNULLNULLDELETENULLNULL1aDELETENULLNULL3bDELETENULLNULL5cUPDATE10I10dTargetIDName10I2E4F6HSourceIDName2E4F6H10I*/go
Oracle环境:
/**删除表beginexecute immediate ' drop table Target';exception when others thennull;end;beginexecute immediate ' drop table Source';exception when others thennull;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.NameWHERE 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/SourceIDNAME10I6H4F2E**/