SQL Server 2008 之 values、Top、Merge

fddd

一、Values

1.多行插入

  建测试表

create table ValuesTest
(
Id int,
Name varchar(20),
Age int
)
go

insert into ValuesTest

--(Id,Name,Age)    全部列 可不写
values
(1,'张三',20),
(2,'李四',30),
(3,'王五',40)

2.临时结果集

select * from
(Values
(1,'张三',20),
(2,'李四',30),
(3,'王五',40)
)
x(id,name,age)



二、Top

1.Top通常用法

例：

select top 2 * from ValuesTest order by id

2.参数化top 数量

例:

declare @tNum int=2
declare @tSql varchar(200)
set @tSql='select top '+CONVERT(varchar,@tNum)+' * from ValuesTest order by id'
exec(@tSql)

注意：直接执行  exec('select top '+CONVERT(varchar,@tNum)+' * from ValuesTest order by id') 是不可以，函数不可以嵌套（提示：关键字 'CONVERT' 附近有语法错误。）

另外：

set rowcount 2
select * from ValuesTest
order by id

可以有相同的效果（据说top 和 rowcount在有索引的情况下，性能差不多，在无序的情况下，top性能比rowcount好，不过本人未测试）

2.删除

delete top(10) from ValuesTest  --注：10必须用括号，此处top相当于函数，10是参数

3.更新

update top(10) ValuesTest  set age=50

或

update top(10) a set a.age=50 from ValuesTest a



三、Merge

1.同时执行增、删、改

新增一个对比表

create table ValuesTest_compare
(Id int,Name varchar(20),Age int)
go

插入数据

insert into ValuesTest_compare values
(1,'张三',20),
(2,'李四',80),
(4,'马六',80)

现在看一下两个表的数据

select * from ValuesTest

Id Name Age
1        张三        20
2        李四        30
3        王五        40



select * from ValuesTest_compare

Id Name Age
1        张三        20
2        李四        80
4        马六        80

ValuesTest与ValuesTest_compare都是3条记录，但是第一、二条记录的Age和ValuesTest_compare不周，第三条记录ValuesTest_compare没有，ValuesTest_compare的第三记录ValuesTest也没有

下面执行

--确定要修改的目标表
Merge Into ValuesTest_compare a
--从数据源查找 并关联（id关联）
using ValuesTest b on a.id=b.id
--如果id相同，则更新目标表
When Matched and a.Name<>b.Name or a.Age<>b.Age Then Update set a.Name=b.Name,a.Age=b.Age
--也可以直接用 When Matched Then Update set a.Name=b.Name,a.Age=b.Age
--如果目标表中不存在，则从数据源插入目标表
When Not Matched By Target Then Insert (id,name,age) values (b.id,b.name,b.age)
--如果目标表的记录在源表中不存在，则删除目标表记录
When Not Matched By Source Then Delete;

（注意：Merge必须以";"结束，否则会有提示  MERGE 语句必须以分号(;)结尾）

结果

(3 行受影响)

ValuesTest_compare表现在数据和ValuesTest相同

2.输出修的改记录

truncate table ValuesTest_compare

insert into ValuesTest_compare
values
(1,'张三',20),
(2,'李四',80),
(4,'马六',80)

建一个表存放结果

create table ValuesTest_output
(Id int,Name varchar(20),Age int)
go

Merge Into ValuesTest_compare a
using ValuesTest b on a.id=b.id
When Matched Then Update set a.Name=b.Name,a.Age=b.Age
When Not Matched By Target Then Insert (id,name,age) values (b.id,b.name,b.age)
When Not Matched By Source Then Delete output deleted.* into ValuesTest_output;
select @@ROWCOUNT as Count1,ROWCOUNT_BIG() as Count2
select * from ValuesTest_output;

结果：

Count1 Count2
4        4

Id Name Age
NULL        NULL        NULL
1        张三        20
2        李四        80
4        马六        80



如果 使用 When Matched and a.Name<>b.Name or a.Age<>b.Age Then Update set a.Name=b.Name,a.Age=b.Age，则只有2条记录，因为两个表第一条记录相同

gqinvs

我是真的爱你的，绝对没有骗你，希望你可以相信我。

shaoqin

是时光不让我任性，所以我现在是一个乖小孩

lishenghan

〆.﹏微小的幸福就在身边，容易满足就是天堂...