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 (если возможно).

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

Ответ 1

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

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.

Также: я думаю, что столбец идентичности намного превосходит и должен использоваться вместо этого. Это не подходит для concurrency для исключительной блокировки целых таблиц. Занижение.

Ответ 2

Вы можете игнорировать порядок, используя 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]