What is Table Value Constructor?

I am sure that many have used this without knowing the term. However it is always better to know the term before applying because that makes sure the implementation is done with right way.

The Table Value Constructor allows us to specify set of row value expressions to into a table. This makes it possible to have set of row value expressions in a single DML statement and use it as a form of a table. Yes, this is what we used with INSERT statement when we need to insert multiple records into the table using a single INSERT statement.

The Table Value Constructor can be used with INSERT statement, MERGE statement (with USING clause) and in the definition of a derived table. Here is an example;

CREATE TABLE dbo.Customer
(
 CustomerID int identity(1,1) primary key
 , CustomerCode char(5) not null
 , Name varchar(200) not null
 , DateOfBirth date not null
);
GO

-- Inserting multiple rows with
-- Table Value Constructor
INSERT INTO dbo.Customer
 (CustomerCode, Name, DateOfBirth)
VALUES
 ('CUS01', 'Jane', '1998-01-04')
 , ('CUS02', 'Knight', '2000-05-23')
 , ('CUS03', 'Sam', '1997-2-11')

-- Using Table Value Constructor
-- with USING clause
MERGE INTO dbo.Customer t
USING (VALUES
  ('CUS01', 'Jane', '1998-02-04')
  , ('CUS04', 'Knight', '2000-05-23')) 
  AS s (CustomerCode, Name, DateOfBirth)
 ON t.CustomerCode = s.CustomerCode
WHEN MATCHED THEN
 UPDATE SET Name = s.Name, DateOfBirth = s.DateOfBirth
WHEN NOT MATCHED BY TARGET THEN
 INSERT (CustomerCode, Name, DateOfBirth)
  VALUES (s.CustomerCode, s.Name, s.DateOfBirth);

-- Using Table Value Constructor as a derived table.
SELECT Customer.CustomerCode, Customer.Name, CustomerCountry.CustomerCode
FROM dbo.Customer 
 INNER JOIN (VALUES ('CUS01', 'USA'), ('CUS04', 'LK')) AS CustomerCountry (CustomerCode, CountryCode)
  ON Customer.CustomerCode = CustomerCountry.CustomerCode