SQL Server 2005 ROW_NUMBER() без ORDER BY

Я пытаюсь вставить из одной таблицы в другую, используя

DECLARE @IDOffset int;
SELECT @IDOffset = MAX(ISNULL(ID,0)) FROM TargetTable

INSERT INTO TargetTable(ID, FIELD)
SELECT [Increment] + @IDOffset ,FeildValue
FROM SourceTable
WHERE [somecondition]

TargetTable.ID не является столбцом идентификаторов, поэтому мне нужно найти способ его автоматического увеличения.

Я знаю, что могу использовать курсор или создать табличную переменную со столбцом идентификаторов и полем FieldValue, заполнить ее, а затем использовать в своем insert into...select, но это не очень эффективно. Я попытался использовать функцию ROW_NUMBER для увеличения, но у меня действительно нет законного поля ORDER BY в SourceTable, которое я мог бы использовать, и я хотел бы сохранить исходный порядок SourceTable (если это возможно).

Кто-нибудь может что-нибудь предложить?


sql sql-server tsql auto-increment sql-server-2005
person Fragilerus    schedule 26.01.2011    source источник
comment
Что такое кластеризованный индекс в исходной таблице? Я предполагаю, что это то, о чем вы говорите, когда говорите об исходном порядке SourceTable? Если это куча, особого порядка нет.   -  person Martin Smith    schedule 27.01.2011

Ответы (2)


arrow_upward
77
arrow_downward

Вы можете избежать указания явного порядка следующим образом:

INSERT dbo.TargetTable (ID, FIELD)
SELECT
   Row_Number() OVER (ORDER BY (SELECT 1))
      + Coalesce(
         (SELECT Max(ID) FROM dbo.TargetTable WITH (TABLOCKX, HOLDLOCK)),
         0
      ),
   FieldValue
FROM dbo.SourceTable
WHERE {somecondition};

Однако обратите внимание, что это всего лишь способ избежать указания порядка и НЕ гарантирует сохранения исходного порядка данных. Существуют и другие факторы, которые могут привести к упорядочению результата, например ORDER BY во внешнем запросе. Чтобы полностью понять это, нужно осознать, что понятие «неупорядоченное (особым образом)» не равнозначно «сохранению первоначального порядка» (который ЕСТЬ упорядочен определенным образом!). Я считаю, что с точки зрения чисто реляционной базы данных последняя концепция не существует, по определению (хотя могут быть реализации баз данных, которые нарушают это, SQL Server не является одной из их).

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

Примечание. Многие люди используют (SELECT NULL), чтобы обойти ограничение «не допускается использование констант в предложении ORDER BY оконной функции». По какой-то причине я предпочитаю 1 NULL.

Кроме того: я думаю, что столбец идентификаторов намного лучше и должен использоваться вместо него. Для параллелизма нежелательно монопольно блокировать целые таблицы. Преуменьшение.

person ErikE    schedule 27.01.2011
comment
@Emtucifor: первый работает нормально. Что касается второго, то дело в том, что он возвращает результат, в любом случае одну строку. Если таблица пуста, она возвращает NULL, поэтому нет необходимости в подвыборке, просто используйте ISNULL() в MAX(), но не внутри нее. Я каким-то образом ухитрился сначала отмахнуться от него, на самом деле, я, возможно, тогда и не придал этому особого значения. Простое совпадение позволило мне узнать иначе, когда я прочитал чей-то пост здесь, на SO (сейчас не помню, какой именно). Я проверил это, прежде чем опубликовать свое исправление. Почему лучше? Ну, это просто выглядит проще, и поэтому легче читать, я думаю. - person Andriy M; 28.01.2011
comment
Это не обязательно правильно, если внешний запрос имеет предложение ORDER BY или в других пограничных случаях, как показано здесь: stackoverflow.com/ q/18961789/521799 - person Lukas Eder; 23.09.2013
comment
@LukasEder Мой ответ был правильным, но, возможно, он был не таким полным, как мог бы быть. Если бы кто-то искал сохранение исходного порядка и не знал, что это бессмысленная концепция в SQL Server, он мог бы попытаться использовать этот код для его получения, но потерпел бы неудачу. Теперь я обновил свой ответ, чтобы решить этот аспект. - person ErikE; 23.09.2013
comment
Отлично, танки для исправления - person Lukas Eder; 23.09.2013
comment
@Мохаммад Анини, я отменил ваше редактирование; Я предпочитаю этот синтаксис, который идеально подходит для SQL Server. Пожалуйста, не редактируйте код, если вы не уверены на 100%, что вам это нужно. - person ErikE; 01.12.2016

arrow_upward
4
arrow_downward

Вы можете игнорировать порядок, используя order by (select null) следующим образом:

declare @IDOffset int;
select  @IDOffset = max(isnull(ID, 0)) from TargetTable

insert  into TargetTable(ID, FIELD)
select  row_number() over (order by (select null)) + @IDOffset, FeildValue
  from  SourceTable
 where  [somecondition]
person Mohammad Anini    schedule 19.07.2016