Table Value Constructors in SQL Server 2008

Table Value Constructors (TVCs) are a useful feature of 2008, allowing you to specify tables of values and expressions. This has all sorts of uses. Users who are stuck with previous versions of SQL Server can play along, since Rob demonstrates that there have, for a long time, been ways of doing this in SQL Server, though less elegantly.

Beginning with SQL Server 2008, you can define table value constructors (TVCs) within your data manipulation language (DML) statements so you can work with subsets of data in a tabular format. A TVC is an expression made up of a VALUES clause and one or more row value expressions, each of which returns a scalar value. Taken together, these values provide data to the DML statement in much the same way a subquery might return a table.

A TVC is made up of one or more columns and one or more rows of data. You can use a TVC as part of the VALUES clause in an INSERT statement, the USING clause in a MERGE statement, or the FROM clause in any DML statement. The following syntax shows the elements that make up a TVC:

VALUES( {DEFAULT|NULL|} [,...n] ) [,...n]

Samples
- SELECT statement


- INSERT statement

- MERGER statement

NOTES: TVC is limited to 1000 rows

(Ref: Simple-talk)