SQL Server 2005 ROW_NUMBER() without ORDER BY

Question

I am trying to insert from one table into another using

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 is not an identity column, which is why I have to find a way to auto-increment it myself.

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).

Can anyone suggest anything?

Answer 1

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};

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 calculating the Max in the query and for adding the lock hints is to prevent errors due to a concurrent process inserting using the same value you plan to use, in between the parts of the query executing. The only other semi-reasonable workaround would be to perform the Max() and INSERT in a loop some number of times until it succeeds (still far from an ideal solution). Using an identity column is far superior. It's not good for concurrency to exclusively lock entire tables, and that is an understatement.

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. What you use is up to you.