INSERT and the Table Variable

I introduced the table data type in Chapter 10 but think it a good idea to show it in action here with the INSERT statement. The variable is created just as you create any variable, with the DECLARE @variable statement. You do not create the table variable as you do a regular or temporary table using the CREATE TABLE statement discussed in Chapter 10. The code

 DECLARE @MyTableVar table (column1 varchar (30))   INSERT Into @MyTableVar SELECT Item From Items   Select * FROM @MyTableVar

creates the table variable named MyTableVar and gives it a column named column1 of type varchar(30). Next the INSERT comes along and inserts all the rows from the Items tables, restricted to the Item column, grabbed by the subordinate SELECT statement. You can then do as you want with the table type, using it as if it were a regular table.

The table is faster than a temporary table, and it makes more sense to use it instead of one when you need to store intermediate results. Often a T-SQL statement, stored procedure, or trigger requires you to store several result sets for evaluation during the process. In the past, these had to be installed as temporary tables to the tempdb, and thus their creation and maintenance were cumbersome and a drain on resources. For example, if you used temporary tables in a stored procedure, you would have to delete the tables before or after (or both) executing the procedure.