Table Value Constructor in SQL Server

The values clause is not new to SQL Server, it is used to hard-code values while inserting records into table. The value constructor is enhanced in SQL 2008 which provides a capability to insert multiple rows in a single statement and it also can be used as derived table for joining with other tables.

Inserting multiple records using table value constructor.

INSERT INTO dbo.Supplier (SupplierId,SupplierName,SupplierEmail)
VALUES
(1,'BSA','warehouse@bsa.com'),
(2,'Honda','warehouse@honda.com'),
(3,'Puma','warehouse@puma.com'),
(4,'Nike','warehouse@nike.com')

Each column is separated by comma with in open and closing bracket, the row is separated by comma after the closing bracket.

Values clause as a derived table

SELECT * FROM 
 (
	VALUES
	(1,'BSA'), 
	(2,'Honda'),
	(3,'Puma'),
	(4,'Nike'),
	(5,'Google'),
	(6,'Microsoft'),
	(7,'Apple')
) AS S (SupplierId,SupplierName) 

It returns the records as table format as shown in the below image.

Table value constructor is used in SELECT statement (to join with other table).

SELECT * FROM Supplier S1 FULL JOIN (VALUES
	(1,'BSA'),
	(2,'Honda'),
	(3,'Puma'),
	(4,'Nike'),
	(5,'Google'),
	(6,'Microsoft'),
	(7,'Apple')
	) AS S2 (SupplierId,SupplierName) 
	ON S1.SupplierId = S2.SupplierId