CODEWITHSUNDEEP
sql-servert-sqlsolrrow-number
Muhammad Hewedy
Muhammad Hewedy

Reputation: 30078

How ROW_NUMBER used with insertions?

I've multipe uniond statements in MSSQL Server that is very hard to find a unique column among the result.

I need to have a unique value per each row, so I've used ROW_NUMBER() function.

This result set is being copied to other place (actually a SOLR index).

In the next time I will run the same query, I need to pick only the newly added rows. So, I need to confirm that, the newly added rows will be numbered afterward the last row_number value of the last time.

In other words, Is the ROW_NUMBER functions orders the results with the insertion order - suppose I don't adding any ORDER BY clause?

If no, (as I think), Is there any alternatives?

Thanks.

Upvotes: 0

Views: 108

Answers (3)

T I
T I

Reputation: 9943

I would suggest you consider 'timestamping' the row with the time it was inserted. Or adding an identity column to the table.

But what it sounds like you want to do is get current max id and then add the row_number to it.

Select col1, col2, mid + row_number() over(order by smt) id
From (
    Select col1, col2, (select max(id) from tbl) mid
    From query
) t

Upvotes: 0

M.Ali
M.Ali

Reputation: 69574

I guess you can do something like this..

;WITH
  cte 
  AS
  (
    SELECT * , rn = ROW_NUMBER() OVER (ORDER BY SomeColumn)
    FROM
    (
    /* Your Union Queries here*/
    )q
  )
  INSERT INTO Destination_Table
SELECT * FROM
CTE  LEFT JOIN Destination_Table
ON CTE.Refrencing_Column = Destination_Table.Refrencing_Column
WHERE Destination_Table.Refrencing_Column IS NULL

Upvotes: 1

Gavin
Gavin

Reputation: 516

Without seeing the sql I can only give the general answer that MS Sql does not guarantee the order of select statements without an order clause so that would mean that the row_number may not be the insertion order.

Upvotes: 1

Related Questions