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