VALUES Clause in SQL Server

In SQL Server, VALUES is a table value constructor that specifies a set of row value expressions to be constructed into a table.

The VALUES clause is often used with INSERT statements to insert data, but it can also be used as a derived table in either the USING clause of the MERGE statement or the FROM clause.

Syntax

VALUES ( <row value expression list> ) [ ,...n ]   
  
<row value expression list> ::=  
    {<row value expression> } [ ,...n ]  
  
<row value expression> ::=  
    { DEFAULT | NULL | expression }

Note that DEFAULT is allowed only in an INSERT statement. DEFAULT forces SQL Server to insert the default value defined for the column. If a default does not exist for the column and the column allows null values, NULL is inserted. DEFAULT cannot be used on identity columns.

Examples

Here are some examples that demonstrate how VALUES can be used in SQL Server.

Used in an INSERT Statement

Here’s an example of using the VALUES clause as part of an INSERT statement:

CREATE TABLE Idiots (
    IdiotId int IDENTITY(1,1) NOT NULL,
    FirstName varchar(50),
    LastName varchar(50)
    );

INSERT INTO Idiots VALUES 
    ('Peter', 'Griffin'), 
    ('Homer', 'Simpson'), 
    ('Ned', 'Flanders');

That created a table called Idiots and inserted three rows into it.

We can now use a SELECT statement to see the new values in the table:

SELECT * FROM Idiots;

Result:

+-----------+-------------+------------+
| IdiotId   | FirstName   | LastName   |
|-----------+-------------+------------|
| 1         | Peter       | Griffin    |
| 2         | Homer       | Simpson    |
| 3         | Ned         | Flanders   |
+-----------+-------------+------------+

When the VALUES clause is used in an INSERT statement, there is a limit of 1000 rows. One way to overcome this is to use VALUES in a SELECT statement to create a derived table. That saves us from having to use multiple INSERT statements or doing a bulk insert.

Used in a SELECT Statement

We can use VALUES to create a derived table in the FROM clause. Here’s a SELECT statement to demonstrate:

SELECT
   FirstName,
   LastName
FROM
   (VALUES 
        (1, 'Peter', 'Griffin'),
        (2, 'Homer', 'Simpson'),
        (3, 'Ned', 'Flanders')
   ) AS Idiots(IdiotId, FirstName, LastName)
WHERE IdiotId = 2;

Result:

+-------------+------------+
| FirstName   | LastName   |
|-------------+------------|
| Homer       | Simpson    |
+-------------+------------+

Derived tables can be used to overcome the 1000 row limit when inserting values into a database.

Used in a MERGE Statement

Here’s an example of VALUES being used in a MERGE statement:

DECLARE @Changes TABLE(Change VARCHAR(20));

MERGE INTO Idiots AS Target  
USING ( VALUES 
            (3, 'Ned', 'Okily Dokily!'), 
            (4, 'Lloyd','Christmas'), 
            (5, 'Harry', 'Dunne')
        ) AS Source ( IdiotId, FirstName, LastName )  
ON Target.IdiotId = Source.IdiotId
AND Target.FirstName = Source.FirstName
WHEN MATCHED THEN
    UPDATE SET FirstName = Source.FirstName, LastName = Source.LastName
WHEN NOT MATCHED BY TARGET THEN
    INSERT (FirstName, LastName) VALUES (Source.FirstName, Source.LastName)
OUTPUT $action INTO @Changes;

SELECT Change, COUNT(*) AS Count  
FROM @Changes  
GROUP BY Change;

Result:

+----------+---------+
| Change   | Count   |
|----------+---------|
| INSERT   | 2       |
| UPDATE   | 1       |
+----------+---------+

In this case, one row was updated and two new rows were inserted, based on the values provided in the VALUES clause.

Here’s the resulting table now:

SELECT * FROM Idiots;

Result:

+-----------+-------------+---------------+
| IdiotId   | FirstName   | LastName      |
|-----------+-------------+---------------|
| 1         | Peter       | Griffin       |
| 2         | Homer       | Simpson       |
| 3         | Ned         | Okily Dokily! |
| 4         | Lloyd       | Christmas     |
| 5         | Harry       | Dunne         |
+-----------+-------------+---------------+