Another use for SQL Server 2008 row constructors

One last SQL syntax post for the evening…

We've all heard about SQL Server 2008 row constructors. They allow syntax like this to work:

CREATE TABLE name_table (name varchar(20), age int);
go
INSERT INTO name_table VALUES ('Bob', 54), ('Mary', 30), ('Sam', 15), ('Buddy', 9);
go

But how about using them as a table source:

SELECT n.name, n.age, tab.species
FROM name_table n
JOIN
(
  VALUES ('Bob', 'person'), ('Mary', 'person'), ('Sam', 'cat'), ('Buddy', 'cat')
) tab (name, species)
ON n.name = tab.name;

You specify a table alias and name the columns, and its just another (synthesized on the fly) table.

2 thoughts on “Another use for SQL Server 2008 row constructors

  1. Hi Scott,

    I just think its a different syntax, and a single example of the syntax. But I’ll have to add this to my list of possible table sources each time I come across the term "table source" in future BOL reads. Or keep it in mind if I need to read someone else’s code (my maintanance/troubleshooting background is showing, I guess 😉

    The join (second example) actually gets a three column rowset, joining a table (originally populated with the new syntax) and a second "table source" populated inline with the new syntax as part of the join. It doesn’t produce the same result as the code you wrote, which is a variation of the first syntax, but attempting to put ‘species’ in the ‘age’ column.

  2. Thanks – hadn’t seen that. I have to say – that’s a horribly complex way to do that through lol. Why not a UNION?

    INSERT INTO name_table
    SELECT ‘Bob’, ‘person’ UNION ALL
    SELECT ‘Mary’, ‘person’ UNION ALL
    SELECT ‘Sam’, ‘cat’

    Easy for even a beginner to understand.

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.