An example of using SQL Server table Variables

(This is a fictitious example. Ignore the transaction)

First, let's look at a simplified document modification example. The structure of the document data table is as follows:
1. morder (ID, remark, createtime)
ID: Ticket No.
Createtime: Creation Time
2. morderdetail (morderid, PKU, qty)
Morderid: Ticket No.
PKU: Product NO.
Qty: Quantity

In the face of this simple requirement, we have the first SQL

View code

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)
……

Currently, there are no problems with the handling of requirements.

Evolving a new demand:
1. morder (ID, remark, createtime)
ID: Ticket No.
Createtime: Creation Time
2. morderdetail (morderid, PKU, qty)
Morderid: Ticket No.
PKU: Product NO.
Qty: Quantity
Remark: Remarks

Some employees can add a remark to the item, while the operator can modify the quantity or adjust the item (add or delete) at any time)
In this case, we cannot insert the morderdetail data by deleting the data first and then inserting it, because it will delete the things that others have added.
(Assume that a single user can delete a product with comments, but not just comments)
Then, after modifying the document, the operator implies several changes:
1. Added some products
2. modified some items
3. Some items have been deleted.

One direct SQL implementation:

-- Update if it exists. If it does not exist, insert it.
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
Delete from morderdetail where morderid = @ ID and PKU not in (@ pku0, @ pku1 ......);

For example, if there are more than 100 products, the amount of SQL code is considerable, and the more goods, the lower the efficiency.

We can use table variables to optimize the implementation:

View code

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)

First, the table variable @ TB is inserted into the data to be updated.

Then:

First, update the existing data in the data table,

Delete nonexistent product of @ TB

Then Insert the new item into the data table.

Table variables are used in a similar way as temporary tables, but you do not need to explicitly Delete temporary tables.

The table variable is in memory, and the temporary table is a physical table. When using it again, it depends on the situation. It is better to process a small amount of data with table variables, but operations with many tasks and a large amount of data are a test of memory.

After repeated tests, it is more efficient to update table variables in batches.