How to use temporary tables in SQL
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
精彩评论