sql 常用临时表的区别(实例演示)

--临时表，tempdb，手工删除
    SELECT v.SONo,v.OrderNo,v.LogisticsCompanyCode,so.OrderId
    INTO #a
    FROM View_CourierReceipt v WITH(NOLOCK)
    LEFT JOIN ShippingOrder so WITH(NOLOCK) ON so.OrderNo = v.OrderNo
    WHERE v.CourierReceiptNo='EMS000000002'
    select * from #a
    select * from #a
    drop table #a

--CTE，内存，自动删除
  with a as(
    SELECT v.SONo,v.OrderNo,v.LogisticsCompanyCode,so.OrderId
    FROM View_CourierReceipt v WITH(NOLOCK)
    LEFT JOIN ShippingOrder so WITH(NOLOCK) ON so.OrderNo = v.OrderNo
    WHERE v.CourierReceiptNo='EMS000000002'
  )
  select * from a
  select * from a  --只能用一次a

--表变量，内存，自动删除
  declare @a table(
    [SONo] nvarchar(20)
    ,OrderNo nvarchar(20)
    ,LogisticsCompanyCode nvarchar(20)
    ,OrderId int
  )
  insert into @a
  SELECT v.SONo,v.OrderNo,v.LogisticsCompanyCode,so.OrderId
    FROM View_CourierReceipt v WITH(NOLOCK)
    LEFT JOIN ShippingOrder so WITH(NOLOCK) ON so.OrderNo = v.OrderNo
    WHERE v.CourierReceiptNo='EMS000000002'
  select * from @a
  select * from @a