Row Constructors in SQL Server 2008

SQL Server 2008 provides a new method to insert data to SQL Server tables, referred to as row constructors. Row constructors are a feature that can be used to simplify data insertion, allowing multiple rows of data to be specified in a single DML statement. Row constructors are used to specify a set of row value expressions to be constructed into a data row.


Row constructors can be specified in the VALUES clause of the INSERT statement, in the USING clause of the MERGE statement, and in the definition of a derived table in the FROM clause. The general syntax of the row constructor is as follows:


VALUES ( { expression | DEFAULT | NULL |} [ ,...n ] ) [ ,...n ]


Each column of data defined in the VALUES clause is separated from the next using a comma. Multiple rows (which may also contain multiple columns) are separated from each other using parentheses and a comma. When multiple rows are specified, the corresponding column values must be of the same data type or implicitly convertible data type. 


The following example shows the row constructor VALUES clause being used within a SELECT statement to define a set of rows and columns with explicit values:

  SELECT a, b 
  FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) )
  AS MyTable(a, b);

Result:

The following example shows the row constructor VALUES clause being used within a Insert statement:

   Create Table #tmpTest
   (
   ID INT,
   TestValue Varchar(20)
   )
   
   INSERT INTO #tmpTest(ID,TestValue)
   Values (1,'Test1'),
 (2,'Test2'),
 (3,'Test3')
 
   Select * from #tmpTest


Result:

The following example shows the row constructor VALUES clause being used within a MERGE statement:

Create Table #tmpTest
   (
   ID INT,
   TestValue Varchar(20)
   )
   
   INSERT INTO #tmpTest(ID,TestValue)
   Values (1,'Test1'),
 (2,'Test2'),
 (3,'Test3')
 
MERGE
     INTO #tmpTest as s
     USING
       (VALUES
          (41   , 'Test41'),
          (125  , 'Test125'),
          (1 , 'Test1')
       ) as i (ID, TestValue)
     ON s.ID = i.ID
     WHEN MATCHED and s.ID <> i.ID
          THEN UPDATE
               SET s.ID = i.ID,
               s.TestValue = i.TestValue
     WHEN NOT MATCHED
          THEN INSERT (ID, TestValue)
          VALUES (i.ID, i.TestValue)
OUTPUT $action,
        isnull(inserted.ID, 0) as src_ID,
        isnull(inserted.TestValue, '') as src_TestValue,
        isnull(deleted.ID, 0) as tgt_ID,
        isnull(deleted.TestValue, '') as tgt_TestValue
;
go


Result: