SQLSERVER merge的简单用法

最新推荐文章于 2024-08-11 21:37:22 发布
最新推荐文章于 2024-08-11 21:37:22 发布
阅读量9.4k 收藏 9
点赞数
 MERGE INTO TEST_IN_COPY3 a USING (
SELECT
'武汉-北京998' AS NAME,
'2017 - 11 - 21 11 : 53 : 49.0000000 + 00 : 00' AS datetimeoffseta,
'2017-11-10 11:52:20' AS datetim2e,
998 AS ID,
'2017-11-07 11:52:10' AS datetimea,
'2017-11-10 00:00:00' AS datea,
5.5600 AS numerica,
5.55 AS floata
) b ON (a.ID = b.ID)
WHEN MATCHED THEN
UPDATE
SET a.NAME = b.NAME,
 a.datetimeoffseta = b.datetimeoffseta,
 a.datetim2e = b.datetim2e,
 a.datetimea = b.datetimea,
 a.datea = b.datea,
 a.numerica = b.numerica,
 a.floata = b.floata
WHEN NOT MATCHED THEN
INSERT (
NAME,
datetimeoffseta,
datetim2e,
ID,
datetimea,
datea,
numerica,
floata
)
VALUES
(
b.NAME,
b.datetimeoffseta,
b.datetim2e,
b.ID,
b.datetimea,
b.datea,
b.numerica,
b.floata

) ;


下面是主要的同步操作

MERGE INTO - 数据的目的地，将数据最终 MERGE 到的表对象

USING 与源表连接 ON 关联的条件

WHEN MATCHED - 如果匹配成功，即关联条件成功 (这时就应该将 SOURCE 中其它的所有字段值更新到 TARGET 表中)

WHEN NOTMATCHED BY TARGET - 如果匹配不成功 (TARGET 中没有这一条记录但是 SOURCE 表有，说明 SOURCE 表多了新数据因此应该插入到 TARGET 表中)

WHEN NOTMATCHED BY SOURCE - 如果匹配不成功 (SOURCE 中没有这一条记录但是 TARGET 表有，说明 SOURCE 表可能把这条数据删除了，所以 TARGET 也应该删除)

MERGE INTO @TargetTable AS T           
USING @SourceTable AS S                
   ON T.ID = S.ID                      
WHEN MATCHED         
   THEN UPDATE SET T.DSPT = S.DSPT  
WHEN NOT MATCHED BY TARGET   
   THEN INSERT VALUES(S.ID,S.DSPT)
WHEN NOT MATCHED BY SOURCE            
   THEN DELETE
OUTPUT $ACTION AS [ACTION],
   Deleted.ID AS 'Deleted ID',
   Deleted.DSPT AS 'Deleted Description',
   Inserted.ID AS 'Inserted ID',
   Inserted.DSPT AS 'Inserted Description'
INTO @Log;

还要注意的是有一些限制条件：

  • 在 Merge Matched 操作中，只能允许执行 UPDATE 或者 DELETE 语句。
  • 在 Merge Not Matched 操作中，只允许执行 INSERT 语句。
  • 一个 Merge 语句中出现的 Matched 操作，只能出现一次 UPDATE 或者 DELETE 语句，否则就会出现下面的错误 - An action of type 'WHEN MATCHED' cannot appear more than once in a 'UPDATE' clause of a MERGE statement.
  • Merge 语句最后必须包含分号，以 ; 结束。

执行一下上面的 MERGE 语句查看一下结果，两个表的数据一模一样了 -

ID = 1,2,3 的记录在 Source 表和Target 表都存在，因此执行的是 UPDATE 操作。

ID = 4，5 的记录在 Source 表存在，但是在 Target 表不存在，因此执行的是 INSERT 操作。

ID = 6，7 的记录在 Target 表存在，但是在 Source 表不存在，因此执行的是 DELETE 操作。

22222222222222

使用merge同时执行insert和update操作

我们经常会有这样的需求，根据某个字段或多个字段查找表中的一行或多行数据，如果查找成功得到匹配项，更新其中的其他一个或多个字段；如果查找失败则将“某个字段或多个字段”作为新的一行中的数据插入到表中。第一种方法是先更新，然后根据@@rowcount判断是否有匹配项，如果没有则插入。先使用下面的 代码创建一个存储过程。

复制代码 
  
  

   
    1 
   
   use
   
    AdventureWorks

   
    2 
   
   
   
   go
   
   

   
    3 
   
   
   
   create
   
    
   
   procedure
   
    dbo.InsertUnitMeasure 
   
   @UnitMeasureCode
   
    
   
   nchar
   
   (
   
   3
   
   ),
   
   @Name
   
    
   
   nvarchar
   
   (
   
   25
   
   )

   
    4 
   
   
   
   as
   
   

   
    5 
   
   
   
   begin
   
   

   
    6 
   
   
   
   set
   
    nocount 
   
   on
   
   ;

   
    7 
   
   
   
   update
   
    Production.UnitMeasure 
   
   set
   
    Name
   
   =
   
   @Name
   
    
   
   where
   
    UnitMeasureCode
   
   =
   
   @UnitMeasureCode
   
   

   
    8 
   
   
   
   if
   
   (
   
   @@ROWCOUNT
   
   =
   
   0
   
   )

   
    9 
   
     
   
   begin
   
   

   
   10 
   
     
   
   insert
   
    
   
   into
   
    Production.UnitMeasure(Name,UnitMeasureCode)
   
   values
   
   (
   
   @Name
   
   ,
   
   @UnitMeasureCode
   
   )

   
   11 
   
     
   
   end
   
   

   
   12 
   
   
   
   end
   
   

   
   13 
   
   
   
   go
复制代码

记得见过这样的笔试题目，要求是插入不存在的行，只要把上面语句中的update改成select就可以了，当时没有写出来，现在恍然大悟，也许是在考察@@ROWCOUNT的用法吧。这个语句也可以使用merge语句实现。下面我们使用merge关键字来修改这个存储过程。

复制代码 
  
  

   
    1 
   
   alter
   
    
   
   procedure
   
    dbo.InsertUnitMeasure 
   
   @UnitMeasureCode
   
    
   
   nchar
   
   (
   
   3
   
   ),
   
   @Name
   
    
   
   nvarchar
   
   (
   
   25
   
   )

   
    2 
   
   
   
   as
   
   

   
    3 
   
   
   
   begin
   
   

   
    4 
   
   
   
   set
   
    nocount 
   
   on
   
   

   
    5 
   
   merge Production.UnitMeasure 
   
   as
   
    target

   
    6 
   
   using (
   
   select
   
    
   
   @UnitMeasureCode
   
   ,
   
   @Name
   
   ) 
   
   as
   
    source (UnitMeasureCode,Name)

   
    7 
   
   
   
   on
   
    (target.UnitMeasureCode
   
   =
   
   source.UnitMeasureCode)

   
    8 
   
   
   
   when
   
    matched 
   
   then
   
    
   
   update
   
    
   
   set
   
    Name
   
   =
   
   source.Name

   
    9 
   
   
   
   when
   
    
   
   not
   
    matched 
   
   then
   
    
   
   insert
   
   (UnitMeasureCode,Name)
   
   values
   
   (source.UnitMeasureCode,Name)

   
   10 
   
   output deleted.
   
   *
   
   ,$action,inserted.
   
   *
   
    
   
   into
   
    MyTempTable;

   
   11 
   
   
   
   end
   
   

   
   12 
   
   
   
   go
复制代码

这个语句使用merge修改存储过程，这个语句中又出现我不太了解的关键字using和$action。Using是用来指定和表InsertUnitMeasure中相匹配的数据源，这里的数据源来自外部输入，是通过两个输入参数得到。$action可能是一个占位符，表示上面的when字句进行的操作








KK凯凯
关注
SQLServer中merge函数用法详解
09-10
Merge关键字是一个神奇的DML关键字。它在SQL Server 2008被引入，它能将Insert,Update,Delete简单的并为一句。通过本篇文章给大家介绍sqlserver中merge函数用法详解，感兴趣的朋友一起学习吧
SQL Server中的Merge关键字
weixin_34346099的博客
03-07 457
简介     Merge关键字是一个神奇的DML关键字。它在SQL Server 2008被引入，它能将Insert,Update,Delete简单的并为一句。MSDN对于Merge的解释非常的短小精悍:”根据与源表联接的结果，对目标表执行插入、更新或删除操作。例如，根据在另一个表中找到的差异在一个表中插入、更新或删除行，可以对两个表进行同步。”,通过这个描述，我们可以看出Merge是关于对于两...
如何在 SQL Server 中使用 `OUTPUT` 子句
网络技术联盟站
08-11 355
OUTPUT子句是 SQL Server 中一个非常有用的功能，能够在执行INSERTUPDATEDELETE和MERGE操作时捕获数据变更的结果。通过掌握OUTPUT子句的使用，可以实现数据审计、日志记录、数据同步等多种功能。了解其基本用法、实际应用场景及注意事项，将帮助你更有效地利用 SQL Server 的数据操作功能。
SQL点滴18—SqlServer中的merge操作，相当地风骚
weixin_33971130的博客
06-22 229
今天在一个存储过程中看见了merge这个关键字，第一个想法是，这个是配置管理中的概念吗，把相邻两次的更改合并到一起。后来在technet上搜索发现别有洞天，原来是另外一个sql关键字，t-sql的语法还是相当地丰富的。本篇是一篇学习笔记，没有什么新意，这里给出technet上的地址连接供大家参考权威：http://technet.microsoft.com/zh-cn/library/bb5106...
【SQLServer】Merge语法
whereabouts_的博客
05-29 1802
MERGE语句，也被称为“upsert”，根据与源表联接的结果，对目标表进行插入、更新或删除操作。例如，根据与另一个表的区别，在一个表中插入、更新或删除行，从而同步两个表。MERGE 语句允许将数据源与目标表或视图联接，然后根据该联接的结果对目标执行多项操作。当两个表具有匹配特性的复杂混合时，针对 MERGE 语句介绍的条件行为的效果最佳。例如，插入不存在的行，或更新匹配的行。如果仅根据另一个表的行更新另一个表时，可以使用 INSERT、UPDATE 和 DELETE 语句来提高性能和可伸缩性。
SQLServer MERGE 用法
暮雪寒寒的随手记
04-15 6149
今天写存储过程的时候，遇到了从一个大json串中取一个id号比对，如果是存在的就更新，不存在就新增。查了资料，觉得还是用merge比较好，这个用法坑也不少，一起来看看代码吧。 MERGE tABatch AS t -- 需要操作的主体表 USING( SELECT batchNo,COUNT(0) AS waitTotal FROM ( SELECT batchNo FROM OPEN...
SQLServer 2008 Merge语句的OUTPUT功能
12-15
下面介绍一下把Output同2008的新T-SQL语句Merge组合使用的方法： 新建下面表： 代码如下:CREATE TABLE Book( ISBN varchar(20) PRIMARY KEY, Price decimal, Shelf int) CREATE TABLE WeeklyChange( ISBN varchar(20...
SQL中Merge用法详解
12-15
在SQL Server、Oracle数据库中可用，MySQL、PostgreSQL中不可用。MERGE是Oracle9i新增的语法，用来合并UPDATE和INSERT语句。通过MERGE语句，根据一张表（原数据表，source table）或子查询的连接条件对另外一张...
sql server merge 的用法
最新发布
09-17
SQL Server的`MERGE`语句（也称为“合并”或“事务性插入/更新”）是一种用于高效地更新数据库中的记录的强大工具，尤其适用于那些需要处理存在冲突情况（如源数据与目标表已有相同键值的数据不一致）的情景。...
sqlserver：merge关键字用法
火焰
12-02 1772
环境： window10 x64 sqlserver 2008 r2 参照： 《SQL Server merge用法》 一、 CREATE TABLE category ( category_id INT PRIMARY KEY, category_name VARCHAR(255) NOT NULL, amount DECIMAL(10 , 2 ) ); INSERT INTO category(category_id, category_name, amount) VALU
SQLServer之merge函数用法
weixin_30727835的博客
11-19 185
MERGE 目标表 USING 源表 ON 匹配条件 WHEN MATCHED THEN 语句 WHEN NOT MATCHED THEN 语句; 其中最后语句分号不可以省略，且源表既可以是一个表也可以是一个子查询语句 WHEN NOT MATCHED BY TARGET表示目标表不匹配，BY TARGET是默认的，所以上面我们直接使用WHEN NOT MATCHED THEN ...
SQL Server Merger实现数据的同步
06-05
用SQL Server的Merger实现数据的同步与合并，非常方便
SQL Server中 MERGE 语句
zxrhhm的博客
06-30 717
SQL Server 中，MERGE 语句用于根据两个表之间的条件来插入、更新或删除记录。
sqlserver——merge用法
zp140024的专栏
02-19 917
using (select id,servicepromotor,servicecmclient from tablea where servicepromotor>0) a--参照表 支持子查询。merge集插入，更新，删除于一体，如果要对一个表同时进行插入，更新，删除2个或3三个操作。效率要高于单个操作。when not matched--a表有 b表匹配不到做insert。when matched --a,b表均匹配到做update。merge into tableb b --被操作表。
sqlserver merge用法
大郎喝药的博客
11-18 724
merge into CE_POLICY_RPL as A using select INSUREDNAME,BALANCE from IN_STAGE_NEW_POL)B on A.INSUREDNAME=B.INSUREDNAME when matched then --条件匹配用update update set A.BALANCE=B.BALANCE,UPDATEDATE=getutcdate() when not matched then --条件不匹配用insert insert .
Sql 中 Merge 的用法
farawayplace613的专栏
02-21 2720
Merge 是Sql2008 新增命令之一，它可以基于两表连接的某个条件的结果操作数据 (INSERT, UPDATE, and DELETE), 它的好处是少写一些代码，多做一些事情， 以下我们将充分说明这个优点。
【SQLSERVER】merge 用法
寒冰的暖
07-31 260
when not matched–a表有 b表匹配不到做insert （也可以加上 by target）when not matched by source–a表没有做delete。using (select * from a) a–参照表 支持子查询。when matched --a,b表均匹配到做update。merge into tableb b --被操作表。on a.id=b.id–关联关系。
SqlServer的Merge Into用法
weixin_34087503的博客
05-14 710
只有SqlServer2008以上版本才支持。 低版本会报incorrect syntax near the keywork 'into'。 select @@version 来查看数据库版本 转载于:https://www.cnblogs.com/taofengfeng/p/10860934.html...
Sql Server 的MERGE用法
预立数据科技
07-19 1900
MERGE可以用作表之间的同步数据 用法如下： 1.有两张数据表分别是sourceTable（源数据表），targetTable（目标数据表） /* create table sourceTable ( id int, name nvarchar(100) ) INSERT INTO sourceTable values(1,'source1') INSERT INTO s...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值