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?

What is the clustered index on the source table? I'm assuming that's what you are talking about when you say "original order of the SourceTable"? If it's a heap there is no particular order. — Martin Smith, Jan 26 '11 at 22:38

ErikE · Accepted Answer · 2022-05-24T01:35:41.207

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.

@Emtucifor: The first one works all right. As for the second, the fact is it does return a result, one row, in any case. If the table is empty, it returns NULL, so no need for subselecting, just use ISNULL() on MAX(), but not inside it. I had somehow managed to dismiss it first, in fact I might not have given it much thinking then. A mere coincidence let me learn otherwise when I read someone's post here on SO (don't remember now which one). I checked it before posting my correction. Why is it better? Well, it's just simpler looking, and so easier to read, I think. — Andriy M, Jan 27 '11 at 21:00
This is not necessarily correct, if the outer query has an `ORDER BY` clause, or in other edge cases as can be seen here: http://stackoverflow.com/q/18961789/521799 — Lukas Eder, Sep 23 '13 at 14:25
@LukasEder My answer was correct, but it was perhaps not as complete as it could have been. If someone were looking for "preserve original ordering" and were unaware that this is a nonsense concept in SQL Server, they might try to use this code to obtain it--but would fail. I have now updated my answer to address this aspect. — ErikE, Sep 23 '13 at 17:44
@Mohammad Anini I have rolled back your edit; I prefer this syntax, which is perfectly valid in SQL Server. Please don't edit code unless you're 100%, absolutely, positively sure that you need to. — ErikE, Dec 01 '16 at 09:53

score 3 · Answer 2 · answered Jul 19 '16 at 07:51

3

You can ignore the ordering by using order by (select null) like this:

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]

answered Jul 19 '16 at 07:51

Mohammad Anini

5,073
4
35
46

All: do not use this answer, because there are no lock hints to ensure that the `max()` result remains valid until the `insert` is completed. Two concurrent clients can get the same max value and then attempt to insert the same value, which will cause the second one to throw a key constraint violation error. Even if the concurrency problem is corrected in this answer, it will then be functionally identical to another answer written 5 1/2 years beforehand, so it adds little value. – ErikE May 24 '22 at 01:32

SQL Server 2005 ROW_NUMBER() without ORDER BY

2 Answers2

Linked