在SELECT INTO中保留ORDER BY

I have a tSQL query that takes data from one table and copies it into a new table but only rows meeting a certain condition:

我有一个tSQL查询，它从一个表中获取数据并将其复制到一个新表中，但只有满足特定条件的行：

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

The code using the table relies on its order, and the copy above does not preserve the order I expected. I.e. the rows in the new table VibeFGEventsAfterStudyStart are not monotonically increasing in the VibeFGEventsAfterStudyStart.id column copied from VibeFGEvents.id.

使用该表的代码依赖于它的顺序，上面的副本不保留我预期的顺序。即新表VibeFGEventsAfterStudyStart中的行在从VibeFGEvents.id复制的VibeFGEventsAfterStudyStart.id列中不会单调增加。

In tSQL how might I preserve the ordering of the rows from VibeFGEvents in VibeFGEventsStudyStart?

在tSQL中，如何在VibeFGEventsStudyStart中保留VibeFGEvents中行的顺序？

4 个解决方案

#1

What for?

做什么的？

Point is - data in a table is not ordered. In SQL Server the intrinsic storage order of a table is that of the (if defined) clustered index.

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

The order in which data is inserted is basically "irrelevant". It is forgotten the moment the data is written into the table.

插入数据的顺序基本上是“无关紧要的”。忘记数据写入表格的那一刻。

As such, nothing is gained, even if you get this stuff. If you need an order when dealing with data, you HAVE To put an order by clause on the select that gets it. Anything else is random - i.e. the order you et data is not determined and may change.

因此，即使你得到这些东西也没有任何收获。如果在处理数据时需要订单，则必须在获取它的选择上放置order by子句。任何其他内容都是随机的 - 即您的数据顺序未确定且可能会发生变化。

So it makes no sense to have a specific order on the insert as you try to achieve.

因此，当您尝试实现时，在插入上具有特定顺序是没有意义的。

SQL 101: sets have no order.

SQL 101：集合没有订单。

#2

I know this is a bit old, but I needed to do something similar. I wanted to insert the contents of one table into another, but in a random order. I found that I could do this by using select top n and order by newid(). Without the 'top n', order was not preserved and the second table had rows in the same order as the first. However, with 'top n', the order (random in my case) was preserved. I used a value of 'n' that was greater than the number of rows. So my query was along the lines of:

我知道这有点旧，但我需要做类似的事情。我想将一个表的内容插入到另一个表中，但是以随机顺序插入。我发现我可以通过使用select top n和newid（）命令来完成此操作。没有'top n'，订单没有保留，第二个表的行与第一个表的顺序相同。然而，对于'top n'，保留了订单（在我的情况下是随机的）。我使用的值'n'大于行数。所以我的查询是这样的：

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

#3

-1

I suspect the query engine is optimising the order by when it's doing the inserts. Don't know if this will work, but it will be a quick experiment

我怀疑查询引擎在进行插入时正在优化顺序。不知道这是否有效，但这将是一个快速的实验

Try

尝试

SELECT rowstoinsert.* 
INTO VibeFGEventsAfterStudyStart 
FROM 
    (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) rowstoinsert

Based on further info

基于进一步的信息

SELECT rowstoinsert.* 
INTO VibeFGEventsAfterStudyStart 
FROM 
    (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) rowstoinsert 
    ORDER BY rowstoinsert.id

Have to be my last guess though and I don't have a sql server available at the moment.

必须是我的最后一个猜测，我目前没有可用的SQL服务器。

#4

-1

Try using INSERT INTO instead of SELECT INTO

尝试使用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`

#1