Thursday, December 27, 2007
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.

SQL Server 2008
Thursday, December 27, 2007 11:07:25 PM (Pacific Standard Time, UTC-08:00)  #    Comments [2]  | 
Friday, December 28, 2007 11:59:15 AM (Pacific Standard Time, UTC-08:00)
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.
Scott Whigham
Friday, December 28, 2007 4:00:44 PM (Pacific Standard Time, UTC-08:00)
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.
Bob Beauchemin
Comments are closed.

Theme design by Jelle Druyts

Pick a theme: