Table Value Constructors were introduced in SQL Server 2008 and enables you to (not only) insert more records with one INSERT statement.

“Standard” option to insert more records is following:

INSERT INTO Continents (Name) VALUES ('Asia')
INSERT INTO Continents (Name) VALUES ('Africa')

With Table Value Constructors you can do this:

INSERT INTO Continents (Name)
VALUES ('Asia'),('Africa')

I must admin that I like it but there’s more. Sometimes you need list of constants in temporary set to be used just within your batch – I was always doing something similar to this (if there was only few values):

SELECT 'Africa'
UNION ALL
SELECT 'Asia'

With Table Value Constructors you can do this:

SELECT *
    FROM (VALUES ('Asia'), ('Africa')) AS Continents (Name)

You can even use subquery within VALUES command. Check full reference and limitations of Table Value Constructors on Technet.

Jakub Dvorak @ www.sqltreeo.com