本文主要是介绍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 Sourcerollback 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 dTarget
ID Name
10 I
2 E
4 F
6 HSource
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.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/Source
ID NAME
10 I
6 H
4 F
2 E
**/
这篇关于SQL2008同Oracle的Merge用法比较的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
