DECLARE @vartable TABLE (TSQL Statement)

creates a new fast temporary table.

Syntax

DECLARE @vartable TABLE
( {     <column_definition>
    | [ <table_constraint> ]
    | [ <table_index> ]  } [ ,...n ]
)

Arguments

@vartable: is the name of the new temporary table. Must start with @.
<column_definition> <table_constraint> <table_index>: see CREATE TABLE.

Remarks

A table variable is a temporary table, that is automatically dropped when the current batch terminates.

You cannot use the statement DROP TABLE on a table variable.
You cannot use the statement TRUNCATE TABLE on a table variable. Use DELETE @vartable if necessary.
You cannot use the statement BULK INSERT on a table variable.

Table variables are very fast, because their data live in a temporary logfile which is written to disk only if the table becomes very large. Small tables live only in memory.

A table variable must be created inside the batch where it is used.

Ordinary DML statements SELECT, INSERT, UPDATE, DELETE can be used on a table variable.

Table variables live outside of explicit transaction. That means that BEGIN TRANSACTION, COMMIT TRANSACTION or ROLLBACK TRANSACTION have no effect on table variables, the same way that these statements have no effect on ordinary variables.

Examples

DROP TABLE t_aux;

CREATE TABLE t_aux (
    id  INT NOT NULL PRIMARY KEY
)

GO

INSERT INTO t_aux VALUES (20);

GO

DECLARE @clients TABLE (
    id         INT NOT NULL PRIMARY KEY,
    name       VARCHAR(30),
    birthdate  DATE
);

INSERT INTO @clients VALUES (10, 'John',  '19700301'),
                      (20, 'Fred',  '20021123'),
                      (40, 'Alex',  '19950712'),
                      (50, 'Maria', '19700322')

UPDATE @clients
SET birthdate = '19000101'
FROM t_aux, @clients c
where c.id = t_aux.id

SELECT *
FROM @clients
ORDER BY name;

Guide