SQL Server 2005 ROW_NUMBER () without ORDER BY

Question

I am trying to insert from one table to 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 identifier column, so I have to find a way to automatically increase it.

I know that I can use the cursor or create a table variable with an identification column and a FieldValue field, fill this in and then use it in my insert into...select , but this is not very efficient. I tried to use the ROW_NUMBER function to increase, but I really do not have a legal ORDER BY field in SourceTable that I can use, and would like to keep the original SourceTable order (if possible).

Can anyone suggest something?

Answer 1

You can omit 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 this is just a way to avoid specifying an order, and DOES NOT guarantee that the original data order will be preserved. There are other factors that can lead to an ordering of the result, for example, ORDER BY in an external query. To fully understand this, you need to understand that the concept of "not ordered (in a certain way)" does not coincide with "maintaining the original order" (which is ordered in a certain way!). I believe that from the point of view of a pure relational database, the latter concept does not exist by definition (although there may be database implementations that violate this, SQL Server is not one of them).

The reason for the lock prompts is to prevent the case where some other process inserts use the value you plan to use between parts of the query being executed.

Note. Many people use (SELECT NULL) to get around "no constants allowed in the ORDER BY clause of a window constraint." For some reason, I prefer 1 over NULL .

Also: I think the identity column is much superior and should be used instead. This is not suitable for concurrency for exclusive locking of entire tables. Understatement.

Answer 2

You can ignore the order using order by (select null) as follows:

 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]