Table Variables
Table variables are objects similar to temporary tables and were introduced in SQL Server 2000. A table variable is declared using the table data type. A statement declaring a table variable initializes the variable as an empty table with a specified structure. As a table definition, such a statement includes definitions of columns with their data type, size, precision, and optional constraints (primary key, identity, unique, and check constraints). All elements have to be defined during the declaration. It is not possible to alter or add them later.
The following batch declares a table variable, inserts rows, and returns them to the user:

Declare @MyTableVar table
     (Id int primary key,
      Lookup varchar(15))

Insert @MyTableVar values (1, '1Q2000')
Insert @MyTableVar values (2, '2Q2000')
Insert @MyTableVar values (3, '3Q2000')

Select * from @MyTableVar
Go

Because of their nature, table variables have certain limitations:

  • Table variables can only be part of the Select, Update, Delete, Insert, and Declare Cursor statements.

  • Table variables can be used as a part of the Select statement everywhere tables are acceptable, except as the destination in a Select...Into statement:
    Select LookupId, Lookup
    Into @TableVariable       -- wrong
    From Lookup
  • Table variables can be used in Insert statements except when the Insert statement collects
    • Requires Free Membership to View

    There are Comments. Add yours.

     
    TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

    REGISTER or login:

    Forgot Password?
    By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
    Sort by: OldestNewest

    Forgot Password?

    No problem! Submit your e-mail address below. We'll send you an email containing your password.

    Your password has been sent to: