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;

view plainprint?

1. CREATE TABLE dbo.Customer  
2. (  
3.  CustomerID int identity(1,1) primary key  
4.  , CustomerCode char(5) not null  
5.  , Name varchar(200) not null  
6.  , DateOfBirth date not null  
7. );  
8. GO  
9.   
10. -- Inserting multiple rows with  
11. -- Table Value Constructor  
12. INSERT INTO dbo.Customer  
13.  (CustomerCode, Name, DateOfBirth)  
14. VALUES  
15.  ('CUS01', 'Jane', '1998-01-04')  
16.  , ('CUS02', 'Knight', '2000-05-23')  
17.  , ('CUS03', 'Sam', '1997-2-11')  
18.   
19. -- Using Table Value Constructor  
20. -- with USING clause  
21. MERGE INTO dbo.Customer t  
22. USING (VALUES  
23.   ('CUS01', 'Jane', '1998-02-04')  
24.   , ('CUS04', 'Knight', '2000-05-23'))   
25.   AS s (CustomerCode, Name, DateOfBirth)  
26.  ON t.CustomerCode = s.CustomerCode  
27. WHEN MATCHED THEN  
28.  UPDATE SET Name = s.Name, DateOfBirth = s.DateOfBirth  
29. WHEN NOT MATCHED BY TARGET THEN  
30.  INSERT (CustomerCode, Name, DateOfBirth)  
31.   VALUES (s.CustomerCode, s.Name, s.DateOfBirth);  
32.   
33. -- Using Table Value Constructor as a derived table.  
34. SELECT Customer.CustomerCode, Customer.Name, CustomerCountry.CustomerCode  
35. FROM dbo.Customer   
36.  INNER JOIN (VALUES ('CUS01', 'USA'), ('CUS04', 'LK')) AS CustomerCountry (CustomerCode, CountryCode)  
37.   ON Customer.CustomerCode = CustomerCountry.CustomerCode  

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