undocumented syntax?

Question

Post reply

undocumented syntax?

toltecman

SSC Enthusiast

Points: 116
More actions
October 2, 2022 at 5:15 pm

#4094593

I recently saw this syntax used in an example - SELECT <col_name> INTO <new_table> FROM (SELECT 'Literal') AS EX(<col_name>); Now I have figured out what it accomplishes but I have yet to find any documentation for the EX(<col_name>) syntax. Can anyone point me to it or is this an undocumented feature?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16154 More actions · Answer 1

its documented even if not clear for some.

https://learn.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql?view=sql-server-ver16

| derived_table [ [ AS ] table_alias ] [ ( column_alias [ ,...n ] ) ]

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 2

Actually, SELECT INTO is very explicitly documented and incredibly useful (especially for building Temp Tables without having to use CREATE TABLE). See the following link...

https://learn.microsoft.com/en-us/sql/t-sql/queries/select-into-clause-transact-sql

To quote from the FROM link that Frederico posted above, there's also a thing called a "Table Valued Constructor", which is incredibly useful for some advanced uses like making inline sequence generators in inline functions, etc...

derived_table
Is a subquery that retrieves rows from the database. derived_table is used as input to the outer query.
derived_table can use the Transact-SQL table value constructor feature to specify multiple rows. For example,
SELECT * FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b);.
For more information, see Table Value Constructor (Transact-SQL).

Here's the link for the "Table Valued Constructor"...

https://learn.microsoft.com/en-us/sql/t-sql/queries/table-value-constructor-transact-sql

EDIT... my apologies... I focused on the wrong issue. 🙁 I was focused on the INTO. The "Table Value Constructor" part is what you're looking for and the example above shows that "AS MyTable(a,b)". Again, the link that Frederico posted also shows that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 3

This was called a singleton SELECT in Standard SQL and it loaded one row into local storage. Microsoft generalized it.

Please post DDL and follow ANSI/ISO standards when asking for help.

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 4

jcelko212 32090 wrote:

This was called a singleton SELECT in Standard SQL and it loaded one row into local storage. Microsoft generalized it.

You need to be a bit specific about what you're talking about, Joe, because we've covered a couple of things on this thread. Are you talking only about the VALUES clause (Table Valued Constructor)? If so, I agree but, IIRC, in 2008, it was changed in SQL Server to allow dozens and even hundreds of rows to be used.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MarkP Old Hand Points: 305 More actions · Answer 5

toltecman wrote:

I recently saw this syntax used in an example - SELECT <col_name> INTO <new_table> FROM (SELECT 'Literal') AS EX(<col_name>); Now I have figured out what it accomplishes but I have yet to find any documentation for the EX(<col_name>) syntax. Can anyone point me to it or is this an undocumented feature?

Was it from this: https://stackoverflow.com/questions/46925282/how-to-return-a-result-as-a-column-value-if-a-row-exists-or-not