How to take the record value according to its position number in SQL SERVER

Question

I’m randomly populating the tables of a database I created and I’m trying to populate a table that has foreign keys. My idea is to create a while loop and get the ID (the values are random) of the line @i + 1 and so populate the other table with the values equal to the ID of that table and so the link works. I tried something like:

    declare @i bigint
    declare @fkid bigint
    set @i = 1
    while @i < 1000
    begin

    select @fkid = ID from TABELA
    WHERE Row_Number() = @i

    insert into tabela2 (id, fk_wds, nome) values (@i, @fkid, @nome)

set @i += 1
    end

It’s not working and I have no idea how to do it, someone can help?

Answer 1

The function Row_Number() is a window function, you cannot use it directly on where of your select.

What you can do is something like this:

SELECT @fkid = r_id
  FROM (SELECT Row_Number() over(ORDER BY id) r_id
          FROM TABELA) tab
 WHERE tab.r_id = @i

The complete example you can find here.

First you calculate the Row_Number in a query and filters it as a sub-query.