开发者

How to use temporary tables in SQL

开发者 https://www.devze.com 2023-01-17 15:12 出处:网络
Please tell me how to make temporary tables in SQL.I am new to this ar开发者_运维知识库ea. CREATE TABLE #Yaks (

Please tell me how to make temporary tables in SQL. I am new to this ar开发者_运维知识库ea.


CREATE TABLE #Yaks (
YakID int,
YakName char(30) )

select name
from tempdb..sysobjects 
where name like '#yak%'

drop table #yaks

Did a Google search and found this as the first hit.


Assuming T-SQL:

DECLARE @ProductTotals TABLE
(
  ProductID int,
  Revenue money
)

INSERT INTO @ProductTotals (ProductID, Revenue)
  SELECT ProductID, SUM(UnitPrice * Quantity)
    FROM [Order Details]
    GROUP BY ProductID

UPDATE @ProductTotals
  SET Revenue = Revenue * 1.15
WHERE ProductID = 62

DELETE FROM @ProductTotals
WHERE ProductID = 60


SELECT TOP 5 *
FROM @ProductTotals
ORDER BY Revenue DESC

That is a table variable. There is also an in-memory table - instead of DECLARE @ProductTotals use CREATE TABLE #ProductTotals.

Use Table Variable for holding data during lifetime of SPROC/Function.

Use In Memory Tables for holding data over multiple SPROC's/child SPROC's. Needs to be explicitly dropped.


SELECT columnNames INTO #temp FROM TableName    
SELECT * FROM #temp    
DROP TABLE #temp
0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号