代码改变世界

SQL server最新技术：Merge及【OFFSET和FETCH】

2012-03-09 17:36  AceYue  阅读(456)  评论(0)  编辑  收藏  举报

Merge关键字在SQL server2008中被引用

    在SQL Server 2008之前没有Merge的时代，基于源表对目标表进行操作需要分别写好几条Insert,Update,Delete。而使用Merge,仅仅需要使用一条语句就好。如下代码：

 1 create table testsource 
 2 (
 3 id int,
 4 [description] varchar(50),
 5 )
 6 go
 7 create table testdatasource
 8 (
 9 id int,
10 [description] varchar(50),
11 )
12 go
13 
14 insert into testsource values(1,'desc1')
15 insert into testsource values(2,'desc2')
16 insert into testsource values(3,'desc3')
17 insert into testsource values(4,'desc4')
18 
19 insert into testdatasource values(1,'update the equal')
20 insert into testdatasource values(2,'update the equal')
21 insert into testdatasource values(5,'delete the not exist in source')
22 insert into testdatasource values(6,'delete the not exist in source')
23 
24 
25 merge into testsource as a
26 using testdatasource as b
27 on a.id=b.id
28 when matched
29 then update set a.[description]=b.[description] --更新相同的id数据
30 when not matched
31 then insert values(b.id,b.[description]) -- 目标表中没有的数据源表中有 将被插入
32 when not matched by source
33 then delete; --目标表中有源表中没有将被删除

Merge语句还有一个强大的功能是通过OUTPUT子句，可以将刚刚做过变动的数据进行输出。

 1 merge into testsource as a
 2 using testdatasource as b
 3 on a.id=b.id
 4 when matched
 5 then update set a.[description]=b.[description] --更新相同的id数据
 6 when not matched
 7 then insert values(b.id,b.[description]) -- 目标表中没有的数据源表中有 将被插入
 8 when not matched by source
 9 then delete --目标表中有源表中没有将被删除
10 output 
11 $action as [action],
12 inserted.id as 插入的id,
13 deleted.id as 删除的id;

可以使用TOP关键字限制目标表被操作的行。

 1 merge top(3) testsource as a
 2 using testdatasource as b
 3 on a.id=b.id
 4 when matched
 5 then update set a.[description]=b.[description] --更新相同的id数据
 6 when not matched
 7 then insert values(b.id,b.[description]) -- 目标表中没有的数据源表中有 将被插入
 8 when not matched by source
 9 then delete --目标表中有源表中没有将被删除
10 output 
11 $action as [action],
12 inserted.id as 插入的id,
13 deleted.id as 删除的id;


 

OFFSET和FETCH关键字在2012中被引用

      SQL Server 2012中在Order By子句之后新增了OFFSET和FETCH子句来限制输出的行数从而达到了分页效果。OFFSET之后指定从哪条记录开始取。其中，取值的数可以是常量也可以是变量或者表达式。 然后通过FETCH关键字指定取多少条记录。这里取的记录条数也可以是常量或者变量表达式。

语法：

ORDER BY order_by_expression
    [ COLLATE collation_name ] 
    [ ASC | DESC ] 
    [ ,...n ] 
[ <offset_fetch> ]


<offset_fetch> ::=
{ 
    OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
    [
      FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY
    ]
}

 实例：

1 --查询testsource表中第1001到1100条记录
2 select * from testsource order by id offset 1000 row fetch next 100 rows only;

 

Be laughted at dream, just have the value of realization.