How to use temporary tables in SQL

Question

Please tell me how to make temporary tables in SQL. I am new to this area.

Answer 1

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.

Answer 2

The two types of temporary tables, local and global, differ from each other in their names, their visibility, and their availability.

Local temporary tables have a single number sign (#) as the first character of their names; they are visible only to the current connection for the user; and they are deleted when the user disconnects from instances of Microsoft® SQL Server™ 2000.

CREATE TABLE #people 
( 
    id INT, 
    name VARCHAR(32) 
)

Global temporary tables have two number signs (##) as the first characters of their names;

CREATE TABLE ##people 
( 
    id INT, 
    name VARCHAR(32) 
)

Temporary Table variable Example (Sql Server)

DECLARE @TibetanYaks TABLE (
YakID int,
YakName char(30) )

INSERT INTO @TibetanYaks (YakID, YakName)
SELECT  YakID, YakName
FROM    dbo.Yaks
WHERE   YakType = 'Tibetan'

More aobut : Temporary Tables

Answer 3

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.

Answer 4

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