在SELECT INTO中保留ORDER BY

Question

我有一个 T-SQL 查询，它会从一个表中获取数据，并将符合特定条件的行复制到一个新表中：

SELECT VibeFGEvents.* 
INTO VibeFGEventsAfterStudyStart 
FROM VibeFGEvents
LEFT OUTER JOIN VibeFGEventsStudyStart
ON 
    CHARINDEX(REPLACE(REPLACE(REPLACE(logName, 'MyVibe ', ''), ' new laptop', ''), ' old laptop', ''), excelFilename) > 0
    AND VibeFGEventsStudyStart.MIN_TitleInstID <= VibeFGEvents.TitleInstID
    AND VibeFGEventsStudyStart.MIN_WinInstId <= VibeFGEvents.WndInstID
WHERE VibeFGEventsStudyStart.excelFilename IS NOT NULL
ORDER BY VibeFGEvents.id

使用该表的代码依赖于其顺序，而上面的复制没有保留我期望的顺序。即从 VibeFGEvents.id 复制到 VibeFGEventsAfterStudyStart.id 的行在新表格 VibeFGEventsAfterStudyStart 中不是单调递增的。

在T-SQL中，我如何保留从 VibeFGEvents 到 VibeFGEventsStudyStart 的行的顺序？

- dumbledad

---

寻找 VibeFGEventsStudyStart.MIN_TitleInstID 并查看所有其他名称是否相同是很痛苦的。 - Royi Namir

它与SQL服务器有什么关系？我在谈论提问问题。我们不关心他的真实姓名，而是关心他的问题。这就是他来这里的原因，要提出问题（更清晰地说，是为了我们这些SO用户）。 - Royi Namir

Royi - 我本可以在帖子中缩短名称，抱歉，尽管在我自己的代码中，出于 Tony 给出的原因，我更喜欢使用长名称。这些数据是一份研究数据的档案，因此我可以使用效率低下的查询方式，因为数据量不大且不会发生变化。 - dumbledad

由于关系型数据库本身并没有任何“顺序”的概念 - 保留插入时的顺序有什么意义呢？一般来说，任何关系表默认情况下都是无序的；只有当你在SELECT语句中显式定义一个ORDER BY子句时，结果集才能被排序。 - marc_s

没有其他方法，因为数据库没有顺序的概念。在SQL中从来没有过。 - TomTom

Answer 1

我知道这篇文章有点老，但是我需要做类似的事情。我想将一个表的内容按照随机顺序插入到另一个表中。我发现可以使用 select top n 和 order by newid() 来实现这个目标。如果没有使用 "top n"，则顺序不会被保留，第二个表中的行的顺序与第一个表相同。但是，使用 "top n"，顺序（在我的情况下是随机的）被保留了。我使用的值'n'大于行数。所以我的查询类似于：

insert Table2 (T2Col1, T2Col2)
  select top 10000 T1Col1, T1Col2
  from Table1
  order by newid()

- Michael

---

感谢您提供实际答案。 - faddison

Answer 2

为什么要这样做？

重点是-表中的数据没有排序。在SQL Server中，表的固有存储顺序是聚集索引（如果定义了）的顺序。

数据插入的顺序基本上是“无关紧要的”。一旦数据写入表中，它就被遗忘了。

因此，即使您获得了这些内容，也不会受益。如果在处理数据时需要有序，则必须在获取数据的选择语句中放置order by子句。其他任何内容都是随机的-即您获取数据的顺序未确定且可能会更改。

因此，尝试实现特定插入顺序是没有意义的。

SQL 101：集合没有顺序。

- TomTom

---

有时候，以特定的默认顺序查看数据只是舒心而已。虽然这并不是必要的，你说得没错，但有些人更喜欢这样做。 - David Wilson

没有聚集索引的表怎么样，比如那些具有UUID主键的表？ - NielsK

或者处理使用主键序列作为其功能的代码。 - Bon

“为什么”对任何人都没用。有很多情况下我需要把数据保存到临时表中，这样几天后我就可以修复数据。这并不是为了永久存储，而是作为数据修复的临时解决方案。无论如何，下一个答案应该是点赞。 - jjthebig1

这不是答案，而是评论。令人失望的是，它作为此类评论获得了25票。 - Juan Perez

Answer 3

只需在 SQL 语句中添加 top，并设置大于实际行数的数字：

SELECT top 25000 * 
into spx_copy
  from SPX
  order by date

- Greg Gum

---

通过添加TOP，我让SQL正常运行。但是，它们仍然像没有“ORDER BY”一样加载到目标表中...我单独测试了该命令，并且作为选择时它仍然可以正确地排序。有什么想法吗？ - JosephDoggie

我敢肯定它之前对我有效，但现在却不行了。添加一个身份列（"SELECT ..., _dummy = identity(int) INTO ..."）解决了我的问题。 - bwperrin

1. 这是一个绝对愚蠢的问题，让微软感到非常困扰。
2. 谢谢Greg :)

- Illegal Operator

Answer 4

我发现一个特定的场景，在这个场景下我们希望新的表格按照列内容的特定顺序被创建：

• 行数非常大(从200到2亿行)，因此我们使用SELECT INTO而不是CREATE TABLE + INSERT，因为需要尽可能快地加载(最小化日志)。我们已经测试过使用跟踪标识610来加载已创建的空表与有聚集索引的表，但仍然比以下方法需要更长时间。
• 我们需要数据按照特定的列顺序排序以提高查询性能，因此我们在加载表后立即创建一个CLUSTERED INDEX。我们放弃了创建非聚集索引，因为它需要另一个读取索引中未包含的数据的操作，并且我们放弃了创建全覆盖非聚集索引，因为它实际上会使所需保留表的空间增加一倍。

如果你设法通过某种方式创建具有“有序”列的表，则创建具有相同顺序的聚集索引所需的时间要比数据未排序时要少得多。有时(你将不得不测试自己的情况)，在SELECT INTO中对行进行排序要比无序加载并稍后创建聚集索引快。

问题在于，当执行INSERT INTO或SELECT INTO时，SQL Server 2012+会忽略ORDER BY列列表。如果在SELECT INTO上指定了一个IDENTITY列，或者插入的表具有IDENTITY列，则它将考虑ORDER BY列，仅用于确定标识值，而不是实际存储顺序。在这种情况下，排序可能会发生，但不是保证的，因为它高度依赖于执行计划。

我们发现的一个技巧是使用UNION ALL的结果执行SELECT INTO，如果你有ORDER BY列表，引擎将执行一个SORT(不总是显式的SORT运算符，有时是MERGE JOIN CONCATENATION等)。这样选择进入就已经按照之后创建聚集索引的顺序创建了新表，从而索引创建所需的时间更少。

因此，你可以重写这个查询：

SELECT
    FirstColumn = T.FirstColumn,
    SecondColumn = T.SecondColumn
INTO
    #NewTable
FROM
    VeryBigTable AS T
ORDER BY            -- ORDER BY is ignored!
    FirstColumn,
    SecondColumn

to

SELECT
    FirstColumn = T.FirstColumn,
    SecondColumn = T.SecondColumn
INTO
    #NewTable
FROM
    VeryBigTable AS T

UNION ALL

-- A "fake" row to be deleted
SELECT
    FirstColumn = 0,
    SecondColumn = 0

ORDER BY
    FirstColumn,
    SecondColumn

我们已经多次使用这个技巧，但我不能保证它总是有效。我只是将其发布为一个可能的解决方法，以防有人遇到类似的情况。

- EzLo

Answer 5

您无法通过ORDER BY实现此操作，但是如果在SELECT INTO之后创建一个基于VibeFGEvents.id的聚集索引，该表将按VibeFGEvents.id在磁盘上排序。

- Cyndi Baker

Answer 6

我在 MS SQL 2012 上进行了一项测试，结果清楚地表明，使用 insert into ... select ... order by 是有意义的。以下是我的操作过程：

create table tmp1 (id int not null identity, name sysname);
create table tmp2 (id int not null identity, name sysname);

insert into tmp1 (name) values ('Apple');
insert into tmp1 (name) values ('Carrot');
insert into tmp1 (name) values ('Pineapple');
insert into tmp1 (name) values ('Orange');
insert into tmp1 (name) values ('Kiwi');
insert into tmp1 (name) values ('Ananas');
insert into tmp1 (name) values ('Banana');
insert into tmp1 (name) values ('Blackberry');

select * from tmp1 order by id;

我得到了这个列表：

• 1 苹果
• 2 胡萝卜
• 3 菠萝
• 4 橙子
• 5 猕猴桃
• 6 菠萝蜜
• 7 香蕉
• 8 黑莓

没有什么意外。然后我用以下方式将tmp1复制到tmp2：

insert into tmp2 (name)
select name
from tmp1
order by id;

select * from tmp2 order by id;

我得到的回复与之前完全相同。苹果对黑莓。 现在反转顺序进行测试：

delete from tmp2;

insert into tmp2 (name)
select name
from tmp1
order by id desc;

select * from tmp2 order by id;

• 9 黑莓
• 10 香蕉
• 11 菠萝
• 12 猕猴桃
• 13 橙子
• 14 菠萝蜜
• 15 胡萝卜
• 16 苹果

因此，当目标表中有一个标识列时，按顺序排序也就有了意义！

- CLS

Answer 7

我发现这种方法有助于解决这个问题：

WITH ordered as
(
    SELECT TOP 1000
    [Month]
    FROM SourceTable
    GROUP BY [Month]
    ORDER BY [Month]
)

INSERT INTO DestinationTable (MonthStart)
(
    SELECT  * from ordered
)

- richardprocter

Answer 8

为什么有人想要这样做（特定顺序）是因为你无法在子查询中定义顺序，所以，想法是，如果你创建一个表变量，然后从该表变量进行查询，你会认为你会保留顺序（比如，将必须按顺序排列的行连接起来-比如用于XML或JSON），但实际上你不能。 那么，你该怎么办呢？ 答案是通过在选择中使用TOP来强制SQL排序（只需选择一个足够高的数字以覆盖所有行）。

- MC9000

---

另一个答案已经提到了这一点。请详细说明为什么使用TOP可以使其工作。 - Elaskanator

这是设计上的需要（这样做也很有直观意义，因为没有返回集的排序，TOP 没有任何意义）。正如我在上面的评论中澄清的那样：“...使用 TOP 强制 SQL 对其进行排序...”（是的，这已经被提到过了，但我希望为其他人澄清一下）。 - MC9000

Answer 9

我遇到了同样的问题，其中一个原因是我需要保留顺序的原因之一是当我尝试使用ROLLUP基于原始数据而不是该列中的平均值来获得加权平均值时。例如，假设我想根据四个商店的销售数量查看利润的平均值？我可以通过创建方程式Profit /＃Units = Avg来轻松实现此目的。现在我在我的GROUP BY中包括ROLLUP，以便我还可以看到所有位置的平均值。现在我想：“这是好信息，但我想按最佳平均值到最差的顺序并将总体保持在列表底部（或顶部）。” ROLLUP将使您失望，因此您需要采取不同的方法。

为什么不基于您需要保留的顺序创建行号呢？

    SELECT OrderBy = ROW_NUMBER() OVER(PARTITION BY 'field you want to count' ORDER BY 'field(s) you want to use ORDER BY')
    , VibeFGEvents.*  
    FROM VibeFGEvents
    LEFT OUTER JOIN VibeFGEventsStudyStart
    ON 
        CHARINDEX(REPLACE(REPLACE(REPLACE(logName, 'MyVibe ', ''), ' new laptop', ''), ' old laptop', ''), excelFilename) > 0
        AND VibeFGEventsStudyStart.MIN_TitleInstID <= VibeFGEvents.TitleInstID
        AND VibeFGEventsStudyStart.MIN_WinInstId <= VibeFGEvents.WndInstID
    WHERE VibeFGEventsStudyStart.excelFilename IS NOT NULL

现在您可以使用表中的 OrderBy 字段来设置值的顺序。我从上面的查询中删除了 ORDER BY 语句，因为它不会影响数据加载到表中的方式。

- MHeath

Answer 10

尝试使用INSERT INTO而不是SELECT INTO

INSERT INTO VibeFGEventsAfterStudyStart 
SELECT VibeFGEvents.* 
FROM VibeFGEvents
LEFT OUTER JOIN VibeFGEventsStudyStart
ON 
    CHARINDEX(REPLACE(REPLACE(REPLACE(logName, 'MyVibe ', ''), ' new laptop', ''), ' old laptop', ''), excelFilename) > 0
    AND VibeFGEventsStudyStart.MIN_TitleInstID <= VibeFGEvents.TitleInstID
    AND VibeFGEventsStudyStart.MIN_WinInstId <= VibeFGEvents.WndInstID
WHERE VibeFGEventsStudyStart.excelFilename IS NOT NULL
ORDER BY VibeFGEvents.id`

- Geo2013

---

一张表没有顺序。请查看此问题中的其他评论。 - usr

我理解在这种情况下表格没有顺序。关键是可以使用带有排序的子查询使用INSERT INTO插入行。 - Geo2013

一个简单的观察：在这种情况下，ORDER BY子句是针对SELECT VibeFGEvents.* FROM ...语句而不是INSERT语句的。 - Bogdan Sahlean

插入的顺序并不重要。在选择时，该顺序已经消失了。你无法再将其“取出”。 - usr

除非第一列有隐式排序，通常可以是顺序整数标识主键。 - Bon

在使用INSERT语句填充IDENTITY列时，使用ORDER BY似乎是有意义的。 - Elaskanator