没有 ORDER BY 的 SQL Server 2005 ROW_NUMBER()
我正在尝试使用
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 不是标识列，这就是为什么我必须自己找到一种方法来自动增加它.
TargetTable.ID is not an identity column, which is why I have to find a way to auto-increment it myself.
我知道我可以使用游标，或者创建一个带有标识列和 FieldValue 字段的表变量，填充它，然后在我的 insert into...select 中使用它，但那是效率不高.我尝试使用 ROW_NUMBER 函数进行递增，但我确实在 SourceTable 中没有可以使用的合法 ORDER BY 字段，并且希望保留 SourceTable 的原始顺序(如果可能).
I know I can use a cursor, or create a table variable with an identity column and a FieldValue field, populate that, then use it in my insert into...select, but that is not very efficient. I tried using the ROW_NUMBER function to increment, but I really don't have a legitimate ORDER BY field in the SourceTable that I can use, and would like to keep the original order of the SourceTable (if possible).
谁能给点建议?
推荐答案
您可以避免指定显式排序，如下所示:
You can avoid specifying an explicit ordering as follows:
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 不是其中之一)他们).
However, please note that is merely a way to avoid specifying an ordering and does NOT guarantee that any original data ordering will be preserved. There are other factors that can cause the result to be ordered, such as an ORDER BY in the outer query. To fully understand this, one must realize that the concept "not ordered (in a particular way)" is not the same as "retaining original order" (which IS ordered in a particular way!). I believe that from a pure relational database perspective, the latter concept does not exist, by definition (though there may be database implementations that violate this, SQL Server is not one of them).
锁定提示的原因是为了防止某些其他进程使用您计划使用的值在执行查询的部分之间插入的情况.
The reason for the lock hints is to prevent the case where some other process inserts using the value you plan to use, in between the parts of the query executing.
注意:许多人使用 (SELECT NULL) 来绕过窗口函数的 ORDER BY 子句中不允许使用常量"的限制.出于某种原因，我更喜欢 1 而不是 NULL.
Note: Many people use (SELECT NULL) to get around the "no constants allowed in the ORDER BY clause of a windowing function" restriction. For some reason, I prefer 1 over NULL.
另外:我认为标识列要好得多，应该改用.独占锁定整个表对并发性不利.轻描淡写.
Also: I think an identity column is far superior and should be used instead. It's not good for concurrency to exclusively lock entire tables. Understatement.
相关文章