#temp Table (Temporary Table) temp table is a temporary table that is generally created to store session specific data. Its kind of normal table but it is created and populated on disk, in the system database tempdb — with a session-specific identifier packed onto the name, to differentiate between similarly-named #temp tables created from other sessions. The data in this #temp table (in fact, the table itself) is visible only to the current scope. Generally, the table gets cleared up automatically when the current procedure goes out of scope, however, we should manually clean up the data when we are done with it. Syntax:
-- create temporary table
CREATE TABLE #myTempTable (
AutoID int,
MyName char(50) )
-- populate temporary table
INSERT INTO #myTempTable (AutoID, MyName )
SELECT AutoID, MyName
FROM myOriginalTable
WHERE AutoID <= 50000
-- Drop temporary table
drop table #myTempTable
@table variable table variable is similar to temporary table except with more flexibility. It is not physically stored in the hard disk, it is stored in the memory. We should choose this when we need to store less 100 records. Syntax:
DECLARE @myTable TABLE (
myName char(50) )
INSERT INTO @myTable (AutoID, myName )
SELECT YakID, YakName
FROM myTable
WHERE AutoID <= 50
We don't need to drop the @temp variable as this is created inside the memory and automatically disposed when scope finishes.