Iterate over a table with a non-int id value

Question

I have a table that I need to write a migration script for to move some data from one table to another. To do this I want to write a script that allows me to iterate over each row in a particular table and then store the values in variables before inserting them into another table.

I have a script so far but at the moment it isn't iterating over each row, rather its checking the same row each time. The rows in the table have a GUID as their Id, so it's not just a case of checking the id of the row against the index in the while loop.

At the moment I'm printing out X rows but each print is identical because the script isn't iterating.

What do I need to do to allow my script to iterate over each row instead?

declare @i int = 1
declare @numRows int
declare @settingId nvarchar(255) = ''
declare @dynamicValue nvarchar(255) = ''
declare @ref nvarchar(255) = ''

select @numRows = COUNT(*) from [set].Settings

while(@i <= @numRows)
begin
    select @settingId = Id, @dynamicValue = DynamicValue, @ref = Ref
    from [set].Settings
    
    print @settingId + ' : ' + @dynamicValue + ' : ' + @ref
    set @i = @i + 1
end

Edit: The purpose of the script is to lift some data from the table it is currently in, manipulate the ref slightly and then insert the data back into another table with the same (or modified) values.

Why do you want to iterate at all? SQL is a set based language, so use a set-based solution. — Thom A, Oct 05 '20 at 11:36
Because it's a throwaway script I need to use once, I'm not too bothered about how I achieve the result. — Jake12342134, Oct 05 '20 at 11:36
That doesn't mean you should iterate on every row; if you have 10 of thousands, or worse millions, of rows then a script that processes that data RBAR is going to take an excruciating long period of time. If you have millions of rows then put them into batches (of say a million) sure, but not one agonising row at a time. — Thom A, Oct 05 '20 at 11:39
As Larnu implies, your first option should be just to `SELECT` all the data out in one go - in your example, you could just write `SELECT Id + ' : ' + DynamicValue + ' : ' + Ref From [set].Settings`. If there's some processing you haven't shown, and you actually need a loop, look up "SQL Cursors", which are a way of looping over a result set; but without more details of what you're trying to do, this isn't really a clear question. — IMSoP, Oct 05 '20 at 11:41
@IMSoP I've added a bit of extra information. There isn't much else to say about it. All I need to know is how to allow my script to iterate over the rows. I tried a cursor but I also couldn't figure out how to take multiple values and store them in multiple variables using one. — Jake12342134, Oct 05 '20 at 11:44
It doesn't look like you *need* to iteratie though, @Jake12342134 . Iterating should always be a last option. Just use a `INSERT INTO...SELECT ... FROM...` — Thom A, Oct 05 '20 at 11:46
@Jake12342134 Well, cursors are definitely the right solution *if* you need to iterate the results, rather than doing it in one `SELECT`. It sounds like your actual problem is a duplicate of this one: https://stackoverflow.com/questions/4974981/get-multiple-values-in-sql-server-cursor — IMSoP, Oct 05 '20 at 11:48
As already pointed out, you don't need to iterate but a simple `INSERT ... SELECT ...` statement. To get help with this you'd need to [edit] the question and include the `CREATE` statements of the tables, some sample data as `INSERT` statements and the rules, which rows should be copied. — sticky bit, Oct 05 '20 at 11:50
A while loop based on min/max logic is just a cursor that is more error-prone. Looping isn't an improvement over a cursor. If you need to iterate, then just use a cursor. — SMor, Oct 05 '20 at 13:06

seanb · Answer 1 · 2020-10-05T12:03:11.750

As comments say - this is MUCH better done as a single direct statement.

For learning purposes (or to get it over with quickly) your issue is that you're not actually telling your SELECT command which row to take - indeed, it takes all rows

begin
    select @settingId = Id, @dynamicValue = DynamicValue, @ref = Ref
    from [set].Settings
    -- NEED A WHERE CLAUSE HERE
    
    print @settingId + ' : ' + @dynamicValue + ' : ' + @ref
    set @i = @i + 1
end

e.g.,

begin
    WITH A AS
        (SELECT Id, DynamicValue, Ref, 
                ROW_NUMBER() OVER (ORDER BY Id, DynamicValue, Ref) AS rn
         FROM [set].[Settings]
         )
    select @settingId = Id, @dynamicValue = DynamicValue, @ref = Ref,  
    from A
    WHERE rn = i
    
    print @settingId + ' : ' + @dynamicValue + ' : ' + @ref
    set @i = @i + 1
end

If any of my coders came to me with this I would send them back. If they came to me twice with it I would send them to HR.

Why? Well, compare the code above (which has omitted all the DECLAREs etc) to this

SELECT   Id + ' : ' + DynamicValue + ' : ' + Ref
FROM     [set].Settings

Done.

PS also beware using SELECT to set variables especially within loops. It is fine, but it has some 'gotchas'. When the value from the SELECT is NULL, it can leave your variable as it was before. It's always good practice imo to SET all your variables to either '' or NULL before you use select to assign them again.

score 0 · Answer 2 · answered Oct 05 '20 at 11:54

If you really have to iterate, assuming your settings.Id is unique you must store this columns' values in a table with identity column added:

declare @map table (id /*settings.Id data type here*/, recid bigint identity(1,1))

then:

insert @map(id) select id from settings

then add condition to select within the loop

select @settingId = Id, @dynamicValue = DynamicValue, @ref = Ref
from [set].Settings
where  Settings.id = (select id from @map where recid = @i)

Iterate over a table with a non-int id value

2 Answers2

Linked