Row constructor in SQL server (Explanation With example)

With SQL server 2008, the new feature was introduced in it to insert multiple rows by writing a single INSERT statement and that is known as Row Constructor.

It saves a lot of time when we are inserting many records into a table.

ROW Constructor or Table Value Constructor means to create a row set by using the VALUES() clause. This allows multiple rows of data to be specified in a single DML statement. And this VALUES() clause can be used with the SELECT, INSERT and MERGE statements.

Before introducing row constructor in SQL server, when we had to add values in multiple, you have to run the sql query like below

-- Before SQL Server 2008 – Multiple INSERT statements required.
INSERT INTO dbo.StudentTable (StudentName, StudentRollNumber)
VALUES ('Chandra', 22)
 
INSERT INTO dbo.StudentTable (StudentName, StudentRollNumber)
VALUES ('Shekhar', 40)
 
INSERT INTO dbo.StudentTable (StudentName, StudentRollNumber)
VALUES ('Manoj', 33)

But after introducing Row constructor in SQL server 2008, adding multiple row became easy and we could do the same operation as above in one query only like below

-- SQL Server 2008 and after (Row constructors)
INSERT INTO dbo.StudentTable (StudentName, StudentRollNumber) -- Single INSERT statement.
VALUES ('Chandra', 22), -- Row 1
      ('Shekhar', 40),  -- Row 2
       ('Manoj', 33) -- Row 3

It can be helpful at various times, let's take a look at each example one by one.

1. You can create a simple set of rows with a SELECT FROM statement:

SELECT * 
FROM (
VALUES
    ('Abhay',1),
    ('Bhanu',2),
    ('Chandra',3)
   
) AS Stu (StudentName, StudentRollNumber);

Here is the Output of the above SQL query

2. You can use it with INSERT statement while inserting rows in a table, we have already explained this example query above, here it is again

INSERT INTO dbo.StudentTable (StudentName, StudentRollNumber) -- Single INSERT statement.
VALUES ('Chandra', 22), -- Row 1
      ('Shekhar', 40),  -- Row 2
       ('Manoj', 33) -- Row 3

You can create the table first using Create table command and insert multiple row values using the above query.

3. You can use them with Joins, without creating #temp tables or Table-Variable to store temporary data (for this example I am using AdventureWorks2012 sample database)

-- Used in an inner join to specify values to return.  
SELECT ProductID, a.Name, Color  
FROM Production.Product AS a  
INNER JOIN (VALUES ('Blade'), ('Crown Race'), ('AWC Logo Cap')) AS b(Name)   
ON a.Name = b.Name;  

output:

4. You can create mixed row-sets from manually entered values and from other tables

CREATE TABLE dbo.Customer (
    CustID      INT, 
    CustName VARCHAR(100), 
    phone       VARCHAR(20), 
    addr        VARCHAR(500)
)


SELECT * 
FROM (
VALUES
    (1, 'customer 1', '(111) 111-1111', 'address 1'),
    (2, 'customer 2', '(222) 222-2222', 'address 2'),
    (3, 'customer 3', '(333) 333-3333', 'address 3'),
    (4, 'customer 4', '(444) 444-4444', 'address 4'),
    ((SELECT CustID FROM dbo.Customer WHERE CustID IN (5)),
     (SELECT CustName FROM dbo.Customer WHERE CustID IN (5)),
     (SELECT phone FROM dbo.Customer WHERE CustID IN (5)),
     (SELECT addr FROM dbo.Customer WHERE CustID IN (5))
    )
) AS C (Name, ListPrice, phone, addr);

Output:

Limitations

1. Table value constructors (Row Constructors) can be used in one of two ways: directly in the VALUES list of an INSERT … VALUES statement, or as a derived table anywhere that derived tables are allowed. Error 10738 is returned if the number of rows exceeds the maximum. To insert more rows than the limit allows, use one of the following methods:

• Create multiple INSERT statements

• Use a derived table

• Bulk import the data by using the BCP utility or the BULK INSERT statement

2. Only single scalar values are allowed as a row value expression. A subquery that involves multiple columns is not allowed as a row value expression. For example, the following code results in a syntax error because the third-row value expression list contains a subquery with multiple columns.