Quick Tip 004 - SQL Row Constructors

Download Source Files: Here

Another excellent addition to SQL Server 2008 was the introduction of the row or table constructors.  In this quick tip I’m going to illustrate how to use the row constructor inside INSERT and SELECT statements.

Some points to note when using the row constructor:

1. The maximum number of rows you can have in a constructor is 1000
2. You cannot use subqueries as the source of a row if it returns more than 1 column or row
3. Data types are determined by earlier rows i the constructor, ensure that the correct data types are defined.
4. If you use a row constructor as the source for an insert you can define NULL and DEFAULT values for columns.
5. You can use row constructors for inserts and updates to data, you can also use them as the candidate for a join.
6. All rows are part of the same transaction if one row fails to be inserted they will all fail.

For more information check out the following link: http://technet.microsoft.com/en-us/library/dd776382.aspx

Examples

Create a sample database.

IF DB_ID('Sample') IS NULL 
CREATE DATABASE Sample;
GO

USE Sample;
GO

Create a sample table.

IF OBJECT_ID('dbo.T1',N'U') IS NOT NULL DROP TABLE dbo.T1;

CREATE TABLE T1
(
   CountryCode VARCHAR(2) DEFAULT 'NA',
   CountryName VARCHAR(50)
);

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.

SELECT 
   CountryCode,
   CountryName 
FROM
(VALUES    ('UK','United Kingdom'),('US','United States'),
       ('FR','France'),('DE','Germany')) AS SRC (CountryName, CountryCode)

 

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.

IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL
DROP TABLE #TEMP

SELECT 
   CountryCode,
   CountryName 
INTO
   #TEMP
FROM
(VALUES    ('UK','United Kingdom'),('US','United States'),
       ('FR','France'),('DE','Germany')) AS SRC (CountryName, CountryCode)

SELECT name, max_length , TYPE_name(system_type_id) AS Type
FROM tempdb.sys.columns WHERE object_id = OBJECT_ID('tempdb..#TEMP')
   
----------------------------------------------------------------------------------

IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL
DROP TABLE #TEMP

SELECT 
   CountryCode,
   CountryName 
INTO
   #TEMP
FROM
(VALUES    (CAST('UK' AS VARCHAR(2)),CAST('United Kingdom' AS VARCHAR(50))),('US','United States'),
       ('FR','France'),('DE','Germany')) AS SRC (CountryName, CountryCode)
   
SELECT name, max_length , TYPE_name(system_type_id) AS Type
FROM tempdb.sys.columns WHERE object_id = OBJECT_ID('tempdb..#TEMP')

You can use the row constructor to create the source for an insert statement.

TRUNCATE TABLE T1;
INSERT INTO T1
VALUES 
   ('UK','United Kingdom'),
   ('US','United States'),
   ('FR','France'),
   ('DE','Germany');

You can also insert DEFAULT and NULL values.

TRUNCATE TABLE T1;
INSERT INTO T1
VALUES 
   ('UK','United Kingdom'),
   ('US','United States'),
   ('FR','France'),
   ('DE','Germany'),
   (DEFAULT, 'Unknown'),
   (NULL,'Missing Country');

Finally, you can use the row constructor as the candidate for a join.

SELECT 
   T1.* 
FROM 
   T1
INNER JOIN (VALUES('UK'),('US')) AS SRC (CountryCode)
   ON SRC.CountryCode = T1.CountryCode