Table Variables…SQL Server 2005

If you have ever created or have used #tempTables in SQL then using Table Variables will be a snap!

the norm for #tempTables are:

CREATE TABLE #tempTable
(
tempID INT NOT NULL,
firstName NVARCHAR(50),
createDate DATETIME DEFAULT (getdate() )
)

INSERT INTO #tempTable VALUES (100,‘Samson’,DEFAULT)
INSERT INTO #tempTable VALUES (200,‘Stephen’,DEFAULT)
INSERT INTO #tempTable VALUES (300,‘David’,DEFAULT)
INSERT INTO #tempTable VALUES (400,‘Peter’,DEFAULT)
INSERT INTO #tempTable VALUES (500,‘Diana’,DEFAULT)

SELECT * FROM #tempTable
DROP TABLE #tempTable



the norm for Table Variables:

DECLARE @tableVariable TABLE
(
tableVarID INT NOT NULL,
firstName NVARCHAR(50),
createDate DATETIME DEFAULT (getdate() )
)

INSERT INTO @tableVariable VALUES (100,‘Samson’,DEFAULT)
INSERT INTO @tableVariable VALUES (200,‘Stephen’,DEFAULT)
INSERT INTO @tableVariable VALUES (300,‘David’,DEFAULT)
INSERT INTO @tableVariable VALUES (400,‘Peter’,DEFAULT)
INSERT INTO @tableVariable VALUES (500,‘Diana’,DEFAULT)

SELECT * FROM @tableVariable
DELETE @tableVariable