«

»

Microsoft SQL SELECT INTO multiple values

Today I came across an interesting construct in Microsoft SQL whereby you are allowed to insert multiple values into a table using a single SELECT INTO statement. Naturally there are more ways of achieving the same goal (SELECT..UNION, CREATE TABLE .. INSERT INTO) but I thought it would be interesting to post this option here anyway, because I like the compactness of the construct.

It’s called a “Row constructor as a derived table” and there’s a very good explanation of it here. If you have multiple values you want to insert (for instance into a temporary table) in one go, you can do so easily by using the following setup:

SELECT *
  INTO #SetDescriptions
  FROM  (VALUES
           ('Apple', 'Fruit')
          ,('Euro', 'Currency')
          ,('Train', 'Transportation')
         ) AS [Descriptions] ( 
                             [Object]
                            ,[BelongsToSet]
                             )

What this will allow you to do is create a table within using an explicit CREATE statement and fill it with values in the same statement.

Tell me what you think! Is this something you would use or is the code too contrived to do any good?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>