問題描述

我有一個典型的非規范化表 (tempTable)，其中包含多個編號的列 (rep1,rep2,...).所以我寫了一個腳本將非規范化數據插入規范化表 (myTable):

I had a typical non-normalized table (tempTable) with multiple numbered columns (rep1,rep2,...). So i wrote a script to insert the non-normalized data into a normalized table (myTable):

insert into myTable
select idRep,rep FROM
(
    select idRep, ISNULL(rep1,'') as rep FROM tempTable
    union
    select idRep, ISNULL(rep2,'') as rep FROM tempTable
    union
    select idRep, ISNULL(rep3,'') as rep FROM tempTable
    union
    select idRep, ISNULL(rep4,'') as rep FROM tempTable
    union
    select idRep, ISNULL(rep5,'') as rep FROM tempTable
) as t

注意:表 myTable 還包含一個自動遞增的 IDENTITY 列作為它的 PRIMARY KEY.

Note: The table myTable also contains an auto-incremented IDENTITY column as its PRIMARY KEY.

在我的場景中，rep1、rep2、rep3、rep4、rep5 的順序很重要.奇怪的是，當我執行腳本時，數據沒有以正確的順序插入，例如自動生成的 id '1000' 的值來自 'rep3'，而 id '1001' 的值來自 'rep1'.

The order rep1, rep2, rep3, rep4, rep5 is important in my scenario. Strangely, when I executed the script, the data wasn't inserted in the correct order such as the auto-generated id '1000' had the value from 'rep3' and the id '1001' had the value from 'rep1'.

這是為什么?腳本是如何執行的?

Why is that? How was the script executed?

推薦答案

在使用 UNION 時它沒有按照您期望的順序進行的原因是 union 試圖強加唯一性，因此它正在處理所有這些行并帶來按照對引擎最方便的順序排列.

The reason it is not going in the order you expect when using UNION is that union attempts to impose uniquness, so it is processing all of those rows together and bringing them out in the order most convenient for the engine.

如果您像 Parado 建議的那樣切換到 UNION ALL(它不會嘗試強加唯一性)，它將不會進行處理，并且它們將按照您放入的順序進入表，幾乎每時每刻.然而，這并不是絕對的，其他進程中發生的某些非常不尋常的情況(尤其是那些以某種方式觸及您的臨時表的情況)可能會影響它.

If you switch to UNION ALL (which does not try to impose uniqueness) as Parado suggested it will not do the processing and they will go into the table in the order you put them in, almost all the time. This however is not gaurunteed and certain very unusual circumstances going on in other processes (especially ones that somehow touch on your tempTable) can affect it.

如果您按照 Kash 的建議使用 order by，那么這將保證 id 的順序(這可能很重要)，但從技術上講，不會確定插入行的順序(這在實踐中很少有影響).

If you use an order by as Kash suggests then that will gauruntee the order of the ids (which can matter), but not technically the order that the rows get inserted (which very rarely matters in practice).

對其中的一些內容進行了很好的總結MSDN.

所以，這就說明了原因.至于如何獲得您真正想要的東西，我會使用 Kash 的建議，即添加一列與 order by 子句一起使用，但我會使用 UNION ALL 而不是 UNION.使用 UNION 就像添加和隱含的distinct"要求一樣，會占用處理器周期并使查詢計劃更加復雜.

So, that takes care of the why. As for the how to get what you actually want, I would use Kash's suggestion of adding a column to use with an order by clause, but I would use UNION ALL instead of UNION. Using UNION is like adding and implicit "distinct" requirement, which takes up processor cycles and makes the query plan more complicated.

這篇關于INSERT INTO SELECT 使用 UNION 的奇怪順序的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！