How can I keep the order of column values in a union select?

Question

I am doing a bulk insert into a table using SELECT and UNION. I need the order of the SELECT values to be unchanged when calling the INSERT, but it seems that the values are being inserted in an ascending order, rather than the order I specify.

For example, the below insert statement

declare @QuestionOptionMapping table
(
    [ID] [int] IDENTITY(1,1)
  , [QuestionOptionID] int
  , [RateCode] varchar(50)
)

insert into @QuestionOptionMapping (
    RateCode
)
select
   'PD0116'
union
select
  'PL0090'
union
select
  'PL0091'
union
select
  'DD0026'
union
select
  'DD0025'

SELECT * FROM @QuestionOptionMapping

renders the data as

(5 row(s) affected)
ID          QuestionOptionID RateCode
----------- ---------------- --------------------------------------------------
1           NULL             DD0025
2           NULL             DD0026
3           NULL             PD0116
4           NULL             PL0090
5           NULL             PL0091

(5 row(s) affected)

How can the select of the inserted data return the same order as when it was inserted?

To answer why it ends up sorted: `UNION` removes duplicates so usually sorts the data along the way. You can use `UNION ALL` to avoid this. — Brian, Jul 03 '18 at 19:02

Eric Brandt · Answer 1 · 2018-07-03T13:40:43.480

SQL Server stores your rows as an unordered set. The data points may or may not be contiguous, and they may or may not be in the "order" the data was specified in your insert statements.

When you query the data, the engine will retrieve the rows in the most efficient order, as determined by the optimizer. There is no guarantee that the order will be the same every time you query the data.

The only way to guarantee the order of your result set is to include an explicit ORDER BY clause with your SELECT statement.

See this answer for a much more in depth discussion as to why this the case. Default row order in SELECT query - SQL Server 2008 vs SQL 2012

By using the SELECT/UNION option for your INSERT statement, you're creating an unordered set that SQL Server ingests as a set, not as a series of inputs. Separate your inserts into discrete statements if you need them to have the IDENTITY values applied in order. Better yet, if the row numbering matters, don't leave it to chance. Explicitly number the rows on insert.

Ok at least now I understand why UNION is causing this issue. Good read, thanks — Rudolf Lamprecht, Jul 04 '18 at 05:39

Gordon Linoff · Answer 2 · 2018-07-04T02:36:41.367

SQL tables do represent unordered sets. However, the identity column on an insert will follow the ordering of the order by.

Your data is getting out of order because of the duplicate elimination in the union. However, I would suggest writing the query to explicitly sort the data:

insert into @QuestionOptionMapping (RateCode)
    select ratecode
    from (values (1, 'PD0116'), 
                 (2, 'PL0090'),
                 (3, 'PL0091'),
                 (4, 'DD0026'),
                 (5, 'DD0025')
         ) v(ord, ratecode)
    order by ord;

Then be sure to use order by for the select:

select qom.*
from @QuestionOptionMapping qom
order by id;

Note that this also uses the values() table constructor, which is a very handy syntax.

You may want to address the `SET IDENTITY_INSERT` issue. – Philip Kelley Jul 03 '18 at 14:00 — Philip Kelley, Jul 03 '18 at 14:00

LukStorms · Accepted Answer · 2018-07-04T07:57:59.290

If you're not selecting from tables?
Then you could insert VALUES, instead of a select with unions.

insert into @QuestionOptionMapping (RateCode) values
 ('PD0116')
,('PL0090')
,('PL0091')
,('DD0026')
,('DD0025')

Or in your query, change all the UNION to UNION ALL.

The difference between a UNION and a UNION ALL is that a UNION will remove duplicate rows. While UNION ALL just stiches the resultsets from the selects together.
And for UNION to find those duplicates, internally it first has to sort them.
But a UNION ALL doesn't care about uniqueness, so it doesn't need to sort.

A 3th option would be to simply change from 1 insert statement to multiple insert statements. One insert per value. Thus avoiding UNION completely.
But that anti-golfcoding method is also the most wordy.

Changing the UNION to UNION ALL was a quick enough fix. Thanks — Rudolf Lamprecht, Jul 04 '18 at 05:37

score 0 · Answer 4 · answered Jul 03 '18 at 13:55

Your problem is you are not putting them in in the order you think. UNION is distinct values only and it will typically sort the values to facilitate the distinct. Run the select statement alone and you will see.

If you insert using values then order is preserved:

insert into @QuestionOptionMapping (RateCode) values
('PD0116'), ('PL0090'), ('PL0091'), ('DD0026'), ('DD0025')

select * from @QuestionOptionMapping order by ID

How can I keep the order of column values in a union select?

4 Answers4