原文:【SQL Server学习笔记】Delete 语句、Output 子句、Merge语句
DELETE语句
-
--建表
-
select * into distribution
-
from sys.objects
-
-
-
--1.当delete语句要关联其他表时与update语句类似，可参考上面update语句的写法
-
-
--2.truncate table语句删除行比delete快很多，不过必须一次删除所有的行(没有where子句)
-
--之所以快是因为记录的日志很少，采用表级别锁。
-
--如果表中有IDENTITY列，会被重置为列定义的种子值4、TOP--1.在一个事务中删除所有记录，此表的记录有1000w条
-
delete from distribution
-
-
--2.通过top每次只删除1000条记录
-
while (select COUNT(*) from distribution) > 0
-
begin
-
delete top (1000)
-
from distribution
-
end
-
-
/*===============================================================
-
比较1和2(不只限于delete，还包括update、insert),2有以下优点：
-
-
1.每次操作1000条，就提交一次，那么产生少量的日志，使日志空间更容易被重用;
-
如果一次删除大量记录，而产生的大量日志可能比整个日志文件还大，
-
那么会引起日志文件的自动增长，会影响性能
-
-
2.分块操作记录，一次锁住更少的记录，占用更少的锁资源，
-
锁定时间更短，操作完成后这些记录可被其他进程访问，并发性更好
-
=================================================================*/
OUTPUT子句
-
create table t(vid int not null,pic varchar(10) not null)
-
-
insert into t
-
values(1,'abc'),
-
(2,'def'),
-
(3,'hjkl')
-
-
--output必须写在where子句之前
-
update t
-
set pic = 'xyz' --更新操作由删除+添加组合的
-
output deleted.vid, --删除的记录
-
deleted.pic,
-
-
inserted.vid, --添加的记录
-
inserted.pic
-
where vid < 100
-
-
--output写在values之前
-
insert into t(vid,pic)
-
output inserted.*
-
values(5,'mn')
-
-
-
declare @temp table(vid int,pic varchar(10))
-
-
delete from t
-
output deleted.vid, --引用所有字段deleted.*
-
deleted.pic into @temp
-
where vid < 100
output子句的一个应用，由于主表和附表是级联删除的，需要实现删除主表记录时，自动保存主表和附表中相关重要字段的值：
-
-
-
--创建主表
-
create table t1(id int primary key,v varchar(10))
-
-
--创建附表，级联删除
-
create table t2
-
(
-
idd int,
-
id int foreign key references t1(id) on delete cascade,
-
vv varchar(20)
-
)
-
-
insert into t1
-
select 1,'a' union all
-
select 2,'b'
-
-
insert into t2
-
select 1,1,'www' union all
-
select 1,2,'****'
-
-
-
--创建存储删除的t1表的字段
-
create table temp_t1_delete(id int,v varchar(10))
-
-
--创建存储删除的t2表的字段
-
create table temp_t2_delete(id int,vv varchar(20))
-
go
-
-
--创建表t2的delete触发器
-
create trigger dbo.trigger_t2_delete
-
on dbo.t2
-
for delete
-
as
-
begin
-
insert into temp_t2_delete(id,vv)
-
select id,vv
-
from deleted
-
end
-
go
-
-
-
--删除主表记录，自动把删除的主表记录，保存在temp_t1_deletei表中
-
delete from t1
-
output deleted.id, --引用所有字段deleted.*
-
deleted.v into temp_t1_delete
-
where id = 1
-
-
-
-
--查询已删除的记录
-
select *
-
from temp_t1_delete t1
-
left join temp_t2_delete t2
-
on t1.id = t2.id
-
/*
-
id v id vv
-
1 a 1 www
-
*/
MERGE语句
-
create table t_org(org_id int,
-
v1 varchar(20),
-
v2 varchar(30));
-
insert into t_org
-
select 1,'org1',''
-
union all
-
select 2,'org2','name2'
-
union all
-
select 3,'org3','name3'
-
union all
-
select 4,'org4','name4'
-
union all
-
select 5,'org5','name5'
-
-
-
create table t_store(org_id int,
-
v1 varchar(20),
-
v2 varchar(30));
-
insert into t_store
-
select 1,'org1',''
-
union all
-
select 2,'org2-t','name2-t'
-
union all
-
select 3,'org3-t','name3-t'
-
union all
-
select 4,'org4-t','name4-t'
-
union all
-
select 5,'org5-t','name5-t'
-
union all
-
select 6,'org6-t','name6-t'
-
union all
-
select 7,'org7-t','name7-t'
-
-
--生成临时表
-
select * into #t_org from t_org
-
select * into #t_store from t_store
-
-
--定义表变量
-
declare @delete_insert_t_org table(
-
change nvarchar(100),
-
org_id int,v1 varchar(20),v2 varchar(30), --删除的
-
org_id_t int,v1_t varchar(20),v2_t varchar(30)) --添加的
-
-
-
;with mm --作为merge语句中using的内部派生表
-
as
-
(
-
select m.org_id,
-
m.v1,
-
m.v2
-
from #t_store m
-
where m.org_id >1
-
)
-
-
--注意：表 with(tablock)，另外通过top关键字只是处理3条记录
-
merge top (3) into #t_org with (tablock) as b
-
using (
-
select *
-
from mm with (tablock) --引用上面CTE公用表表达式产生的内部派生表
-
) m
-
on m.org_id = b.org_id --为了区分是否需要修改，可以增加一个字段来区分，
-
--但是这个字段不应该作为关联条件，
-
--因为会导致接下来运行的merge分块语句把刚才目标表中update过的那条记录，
-
--重复插入目标表中，而是写在when的条件中
-
-
when matched and b.v1 <> m.v1 and isnumeric(m.org_id) = 1 --可以在这里写：区分字段过滤条件
-
then update set v1 = m.v1,v2 = m.v2
-
-
when not matched by target --目标表中没有
-
then insert (org_id,v1,v2) values(m.org_id,m.v1,m.v2) --不可通过values关键字一次添加多列
-
-
when not matched by source --源表中没有
-
then delete
-
-
output $action, --操作：delete、insert、update
-
inserted.org_id,
-
inserted.v1,
-
inserted.v2 , --可改为inserted.*
-
-
deleted.org_id,
-
deleted.v1,
-
deleted.v2 --可改为deleted.*
-
-
INTO @delete_insert_t_org --output的输出放入表变量中
-
-
--关联提示
-
option (loop join); --注意：merge必须以分号结尾
-
-
-
select * from @delete_insert_t_org
