Table Value Constructors

So I was poking around on SQLServerCentral today and I wandered into the QotD section. At random, I picked a TSQL question that introduced me to a new functionality in SQL Server 2008: table value constructors (TVC). Let me state for the record that I am not a developer (in case you hadn’t already noticed). So if I’m the last person to hear about this feature, and you’re all “duh, where the hell have you been?” feel free to move along. If you haven’t heard of this, read on.

In a nutshell, a TVC is a way of specifying multiple rows of data in a single DML statement. For example, suppose you had a table MyBooks and you wanted to insert several records into the table. In previous versions of SQL Server, you’d either have to use multiple insert statements, or a single insert with multiple SELECTs merged with a UNION ALL.

CREATE TABLE #myBooks (
Title		varchar(100),
Author		varchar(100),
Published	varchar(4))

INSERT INTO #mybooks values('Gone With The Wind', 'Margaret Mitchell', '1936');
INSERT INTO #mybooks values('Go Dog Go', 'P.D. Eastman', 1966);
INSERT INTO #mybooks values('The Holy Bible', 'God', '');
GO

--OR

INSERT INTO #mybooks
SELECT 'Gone With The Wind', 'Margaret Mitchell', '1936'
UNION ALL
SELECT 'Go Dog Go', 'P.D. Eastman', 1966
UNION ALL
SELECT 'The Holy Bible', 'God', ''
GO

But with a TVC, you can insert multiple rows using a single INSERT statement.

DELETE #mybooks
INSERT into #myBooks VALUES ('Gone With The Wind', 'Margaret Mitchell', '1936')
							, ('Go Dog Go', 'P.D. Eastman', 1966)
							, ('The Holy Bible', 'God', '')
GO

Be careful, though. If we query #mybooks we’ll see another new feature of SQL Server 2008.

SELECT * FROM #mybooks
GO

Take a look at the published column for the Bible row. 0? But we inserted an empty string into a varchar column, right? In SQL 2008, you now have to be careful of implicit conversions when using a TVC (or an INSERT…SELECT…UNION ALL for that matter). From BOL:

The values specified in a multi-row INSERT statement follow the data type conversion properties of the UNION ALL syntax. This results in the implicit conversion of unmatched types to the type of higher precedence. If the conversion is not a supported implicit conversion, an error is returned.

So even though the destination column is a varchar datatype, because you’re inserting an integer (1966) SQL Server will implicitly convert all of the other values to an integer before inserting them into the varchar. (I’m sure there’s a reason for this, but it doesn’t make a lot of sense to me.)

With that in mind, if we try to execute the following insert, we should get an error.

INSERT into #myBooks VALUES ('Gone With The Wind', 'Margaret Mitchell', '1936')
							, ('Go Dog Go', 'P.D. Eastman', 1966)
							, ('The Holy Bible', 'God', 'c.34')
GO

And we do.

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ‘c.34’ to data type int.

But wait, there’s more! TVCs aren’t limited to INSERT statements. You can also use a TVC in the FROM clause of a query.

SELECT * FROM (VALUES('Gone With The Wind', 'Margaret Mitchell', '1936')
							, ('Go Dog Go', 'P.D. Eastman', 1966)
							, ('The Holy Bible', 'God', '')) as somebooks (title, author, published)
GO

Or in a MERGE statement.

You probably expected an example of a MERGE statement using a TVC here.  But you're not going to get one because I'm not well-versed in MERGE statements.  Hey, I said I wasn't a developer.