SQL SERVER表變量使用的一個示例

（這是一個虛構的例子,請忽略事務）

首先，我們來看一個簡化單據修改的例子，單據數據表結構如下：
1. MOrder（id,remark,createtime）
id：單號
createtime：創建時間
2. MOrderDetail (MOrderid,pku,qty)
MOrderid：單號
pku：商品編號
qty：數量

面對這個簡單的需求，我們有第一版的SQL

update MOrder set remark=@remark
where id=@id
delete from MOrderDetail
where MOrderid=@id
insert into MOrderDetail  values(@id,@pku0,@qty0)
insert into MOrderDetail  values(@id,@pku1,@qty1)
……

針對需求，目前的處理沒有什麼問題。

演化一個新的需求：
1. MOrder（id,remark,createtime）
id：單號
createtime：創建時間
2. MOrderDetail (MOrderid,pku,qty)
MOrderid：單號
pku：商品編號
qty：數量
remark：備注


某些員工可以在商品上加一個備注，而制單人允許隨時修改數量，或調整商品（增刪）
此時，我們不能通過先delete，然後再insert的方式插入MOrderDetail數據，因為那會刪掉別人添加的東西
（假設允許制單人連商品帶注釋一起刪掉，但不能只刪注釋）
那麼制單人在修改單據後，就隱含了幾種變動：
1. 新增了一些商品
2. 修改了一些商品
3. 刪除了一些商品

一個直接SQL實現：

--存在則更新，不存在則插入
If  exists(select  1 from MOrder with(nolock) where MOrderid=@id and pku=@pku0)
         update MOrderDetail set  qty=@qty0
            where MOrderid=@id and pku=@pku0
Else
         insert into MOrderDetail  values(@id,@pku0,@qty0)
……

--刪除
Delete from MOrderDetail where MOrderid=@id and pku not in(@pku0,@pku1……);

比如有100個以上的商品，我們的sql代碼量就很可觀了，而且商品越多效率將越低下。

我們來用表變量優化一下實現：

declare @tb table
(
   pku int,
       qty    int
)
Insert into @tb values(@pku0,@qty0)
Insert into @tb values(@pku1,@qty1)
……

Update a set qty=b.qty
From MOrderDetail as a
inner join @tb as b on a.pku=b.pku
Where a.MOrderid=@id


Delete from MOrderDetail
Where pku not in(select pku from @tb) and MOrderid=@id


Insert into MOrderDetail
Select @id,a.pku,b.qty from @tb
Where pku not in
(select pku from MOrderDetail where MOrderid=@id)

首先，我們將要更新的數據插入了表變量@tb

然後：

先關聯更新數據數據表已存在的數據，

刪除@tb的不存在商品

再將新增的商品插入數據表。

表變量的使用方式，與臨時表類似，但是不需要你顯式刪除臨時表。

表變量是在內存中，臨時表是物理表。再使用的時候，視情況而定，表變量處理小量數據更好，但是任務多、數據量大的操作對內存是個考驗。

經過反復測試，表變量做這種批量更新效率更好。