Quick Tip - Using SQL Row Constructors

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:

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 in 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.

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');

SELECT * FROM T1
        

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');

SELECT * FROM T1
        

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

        

---------------------------------------------------------------------------------------------------------------

Need help with data analytics challenges in your organisation. Email: [email protected]