SQL Row Constructors
Introduction
One feature in SQL Server that we find lots of developers dont know about is row and table constructors. In this quick tip we are going to illustrate how to use the row constructor inside INSERT and SELECT statements.
Some points to note when using the row constructor:
- The maximum number of rows you can have in a constructor is 1000
- You cannot use subqueries as the source of a row if it returns more than 1 column or row
- Data types are determined by earlier rows in the constructor, ensure that the correct data types are defined.
- If you use a row constructor as the source for an insert you can define NULL and DEFAULT values for columns.
- You can use row constructors for inserts and updates to data, you can also use them as the candidate for a join.
- All rows are part of the same transaction if one row fails to be inserted they will all fail.
Examples
Create a sample database.
Create a sample table.
Simple select statement using the row constructor. Rows are denoted by open and closed brackets(), the fields in the rows are separated by comma’s. At the end I’ve aliased the table constructor and defined the column names.
If you want to explicitly define the column data types then you need to do so as part of the row constructor. Run the following code, you’ll notice that in the first query SQL Server implies the column data types based on the data within the rows. In the second query we’ve explicitly defined the column data types in the first row and can see that these have been applied when creating the #TEMP table.
You can use the row constructor to create the source for an insert statement.
You can also insert DEFAULT and NULL values.
Finally, you can use the row constructor as the candidate for a join.
