SQL with Manoj

Constructors, as the name suggests means to create an instance of an Object in any Object Oriented Programming language.
 

Here in SQL Server or T-SQL, 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.
 

In the examples below we will see how they can be used and are helpful at times:
 

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

SELECT * 
FROM (
VALUES
	(1, 'cust 1', '(111) 111-1111', 'address 1'),
	(2, 'cust 2', '(222) 222-2222', 'address 2'),
	(3, 'cust 3', '(333) 333-3333', 'address 3'),
	(4, 'cust 4', '(444) 444-4444', 'address 4'),
	(5, 'cust 5', '(555) 555-5555', 'address 5')
) AS C (CustID, CustName, phone, addr);

Usage #2. You can use it with INSERT statement while inserting rows in a table:

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

INSERT INTO dbo.Customer (CustID, CustName, phone, addr)
VALUES
(1, 'cust 1', '(111) 111-1111', 'address 1'),
(2, 'cust 2', '(222) 222-2222', 'address 2'),
(3, 'cust 3', '(333) 333-3333', 'address 3'),
(4, 'cust 4', '(444) 444-4444', 'address 4'),
(5, 'cust 5', '(555) 555-5555', 'address 5');

Usage #3. You can create mixed row-sets from manually entered values and from other tables:

SELECT * 
FROM (
VALUES
	(1, 'cust 1', '(111) 111-1111', 'address 1'),
	(2, 'cust 2', '(222) 222-2222', 'address 2'),
	(3, 'cust 3', '(333) 333-3333', 'address 3'),
	(4, 'cust 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 (CustID, CustName, phone, addr);

Usage #4. You can use them with JOINS, without need to create #Temp-Table or Table-Variable to store temporary data:

SELECT 
	C.CustName, 
	O.ProductName
FROM dbo.Customer C
LEFT JOIN (
VALUES 
	(101, 1, 'Apple'), 
	(102, 3, 'Orange'),
	(103, 5, 'Banana')
) AS O (OrderID ,CustID, ProductName)
ON O.CustID = C.CustID

Usage #5. You can use them with MERGE statement, again without need to create #Temp-Table or Table-Variable to store temporary data:

MERGE INTO dbo.Customer as Target
USING (
VALUES
	(5, 'cust 5', '(555) 555-5555', 'address 5 updated'),
	(6, 'cust 6', '(666) 666-6666', 'address 6')
) AS Source (CustID, CustName, phone, addr)
ON Target.CustID = Source.CustID
WHEN MATCHED THEN UPDATE SET 
	Target.CustName = Source.CustName, 
	Target.phone = Source.phone, 
	Target.addr = Source.addr
WHEN NOT MATCHED BY Target THEN
INSERT (
	CustID, 
	CustName, 
	phone, 
	addr
) 
VALUES (
	Source.CustID, 
	Source.CustName, 
	Source.phone, 
	Source.addr
);

select * from  dbo.Customer;

Thus, Row Constructors or Table Value Constructors are very handy when dealing with fixed set of row sets used for temporary purpose, without need of creating and storing them in #Temp-Tables or Table-Variables.
 

–> Final Cleanup

DROP TABLE dbo.Customer