SELECT a, b FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b);
When I first saw this syntax, I wasn't sure if it would work. However, after testing, this worked perfectly.
This tsql statement could save alot of time for the people that don't need to create a permanent object in the database, but is looking for a quick fix.
This statement could be used as a function or it could be used to select multiple values to insert into a table.
Here is a situation where it could be used in the place of a function.
EX:
Select @dCollected = (SELECT a, b FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b))
Here is a situation where it could be used in the place filling a temporary table and selecting from it.
EX:
SELECT a, b FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b)
Union
Select a, b From PermanentOrTempTable
This is a good use of this syntax.
However, I would not recommend using this often as it could lead to spagetti code and cannot be reused unless pasted over and over again which is bad development.
Reference: https://msdn.microsoft.com/en-us/library/dd776382.aspx
No comments:
Post a Comment