Сохранение ORDER BY в SELECT INTO

Ответ 1

Зачем?

Дело в том, что данные в таблице не упорядочены. В SQL Server внутренний порядок хранения таблицы соответствует порядку (если он определен) кластерного индекса.

Порядок, в котором вставляются данные, в основном "не имеет значения". Забывается момент, когда данные записываются в таблицу.

Таким образом, ничего не получится, даже если вы получите этот материал. Если вам нужен порядок при работе с данными, вы ДОЛЖНЫ поместить заказ по предложению в выборку, которая его получает. Все остальное является случайным - т.е. порядок данных и данных не определены и могут измениться.

Так что нет смысла иметь конкретный порядок на вставке, как вы пытаетесь достичь.

SQL 101: наборы не имеют порядка.

Ответ 2

Я знаю, что это немного устарело, но мне нужно было сделать что-то подобное. Я хотел вставить содержимое одной таблицы в другую, но в произвольном порядке. Я обнаружил, что могу сделать это, используя select top n и order by newid(). Без "верхнего n" порядок не сохранялся, а вторая таблица имела строки в том же порядке, что и первый. Однако при "верхнем n" порядок (случайный в моем случае) был сохранен. Я использовал значение "n", которое было больше, чем количество строк. Поэтому мой запрос был следующим:

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

Ответ 3

Вы не можете сделать это с помощью ORDER BY, но если вы создадите кластерный индекс на VibeFGEvents.id после вашего SELECT INTO, таблица будет отсортирована на диске по VibeFGEvents.id.

Ответ 4

Причина, по которой можно этого пожелать (конкретный порядок), заключается в том, что вы не можете определить порядок в подзапросе, поэтому идея заключается в том, что, если вы создадите табличную переменную, ТО, сделав запрос из этой табличной переменной, вы будете думать, что сохранит порядок (скажем, для конкатенации строк, которые должны быть в order- скажем для XML или json), но вы не можете. Ну так что ты делаешь? Ответ заключается в том, чтобы заставить SQL упорядочить его, используя TOP в вашем выборе (просто выберите число, достаточно высокое, чтобы охватить все ваши строки).

Ответ 5

Я нашел конкретный сценарий, в котором мы хотим, чтобы новая таблица создавалась с определенным порядком в содержимом столбцов:

• Количество строк очень велико (от 200 до 2000 миллионов строк), поэтому мы используем SELECT INTO вместо CREATE TABLE + INSERT потому что нужно загружать как можно быстрее (минимальное ведение журнала). Мы протестировали использование флага трассировки 610 для загрузки уже созданной пустой таблицы с кластеризованным индексом, но все же занимает больше времени, чем при следующем подходе.
• Нам нужно упорядочить данные по определенным столбцам для выполнения запросов, поэтому мы создаем CLUSTERED INDEX сразу после загрузки таблицы. Мы отказались от создания некластеризованного индекса, потому что для этого потребовалось бы другое чтение данных, не включенных в упорядоченные столбцы из индекса, и мы отказались от создания полностью охватывающего некластеризованного индекса, поскольку он практически удвоил бы объем необходимого пространства. держать стол.

Бывает, что если вам удается каким-то образом создать таблицу с уже "упорядоченными" столбцами, создание кластерного индекса (с тем же порядком) занимает намного меньше времени, чем когда данные не упорядочены. И иногда (вам придется проверить ваш случай), порядок строк в SELECT INTO выполняется быстрее, чем загрузка без заказа и создание кластеризованного индекса позже.

Проблема в том, что SQL Server 2012+ будет игнорировать список столбцов ORDER BY при выполнении INSERT INTO или при выполнении SELECT INTO. Он будет учитывать столбцы ORDER BY если вы укажете столбец IDENTITY в SELECT INTO или если во вставленной таблице есть столбец IDENTITY, а просто для определения значений идентификаторов, а не фактического порядка хранения в базовой таблице. В этом случае вполне вероятно, что сортировка произойдет, но не гарантируется, поскольку она сильно зависит от плана выполнения.

Уловка, которую мы обнаружили, заключается в том, что выполнение SELECT INTO с результатом UNION ALL заставляет механизм выполнять SORT (не всегда явный оператор SORT, иногда MERGE JOIN CONCATENATION и т.д.), Если у вас есть список ORDER BY. Таким образом, выборка в уже создает новую таблицу в том порядке, в котором мы собираемся создать кластеризованный индекс позже, и, таким образом, создание индекса занимает меньше времени.

Таким образом, вы можете переписать этот запрос:

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

в

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

Мы использовали этот трюк несколько раз, но я не могу гарантировать, что он всегда будет сортировать. Я просто публикую это как возможное решение, если у кого-то есть подобный сценарий.

Ответ 6

Я столкнулся с той же проблемой, и одна из причин, по которой мне нужно было сохранить ордер, заключается в том, что я пытаюсь использовать ROLLUP для получения средневзвешенного значения на основе необработанных данных, а не среднего значения того, что находится в этом столбце. Например, скажем, я хочу увидеть среднюю прибыль, основанную на количестве единиц, проданных четырьмя магазинами? Я могу сделать это очень легко, создав уравнение Profit/#Units = Avg. Теперь я включил ROLLUP в свою GROUP BY, чтобы я также мог видеть среднее по всем местоположениям. Теперь я думаю про себя: "Это хорошая информация, но я хочу видеть ее в порядке" Лучшее среднее значение для ухудшения "и держать итоговые значения в нижней (или верхней) части списка". 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 из запроса выше, так как он не влияет на то, как данные загружаются в таблицу.

Ответ 7

Попробуйте создать идентификатор Sqnc

Declare @tmptable as table(Sqnc int identity,Code varchar(35),Detail int)

INSERT INTO @tmptable 
SELECT Code,Detail From Table2 a

Ответ 8

Я подозреваю, что механизм запросов оптимизирует порядок, когда он делает вставки. Не знаю, будет ли это работать, но это будет быстрый эксперимент.

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

Основываясь на дополнительной информации

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

Должен быть моим последним предположением, хотя и у меня нет сервера sql, доступного на данный момент.

Ответ 9

Попробуйте использовать 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`