Advertisements
Home > SQL Basics > SQL Basics – What are Row Constructors?

SQL Basics – What are Row Constructors?


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

Row Constructor 01
 

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;

Row Constructor 02
 

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

Advertisements
  1. No comments yet.
  1. July 21, 2015 at 7:18 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: