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