have you any wool?

Many times you need to create and use a table temporarily in SQL query or Stored Procedure and then destroy it when you’re done. A lot of times I see people using Temporary Tables to do this, but I’ve always opted for the table variable approach. For a quick run down on some of the pros and cons of Table Variables, check out this link.

To create a table variable, you do not write a Create Table statement. Instead, you use the Declare keyword to the define the variable the same as any other variable in SQL. However, when you define its type, you set it to table and then specify your colum definitions.

Declare @Test Table(
     [ID] int identity(1,1) primary key,
     [Name] varchar(50)
)

Insert Into @Test values ('John')
Insert Into @Test values ('Jim')
Insert Into @Test values ('Joe')
Insert Into @Test values ('Jacob')

Select *
From @Test