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 < 100output子句的一個應用，由於主表和附表是級聯刪除的，需要實現刪除主表記錄時，自動儲存主表和附表中相關重要欄位的值：
--建立主表
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,'csdn'
--建立儲存刪除的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