I have a temporary table with data in it. I want to look at each row separately, like a cursor. But not since cursors are very hard, and very maligned.
If the table had an id column in it, and the IDs were a series, i could create a for loop with a counter, and add the the counter, repeat until done.
When i create the table, i can add an identity column that starts at one and increases each row. But then you say i have to “insert” into it. Nay, i say, you can still ‘select into’ to make the table – remember you don’t have to select * into #temp, you don’t have to use all the columns in the source table. AND you can add columns also. Consider
select identity(int, 1, 1) as rowid , rtrim(source) as source , rtrim(dest) as dest , rtrim(copy) as copy , rtrim(partition) as partition into #copy_tables from dbo.load_tables
We have 5 columns, four of them from load_tables, and one of them made on the fly. You can always do this even if you have the data in an existing table, just select into another temp table and you are GBD (golden brown and declicious).
In my case, i wasn’t so smart. I got a distinct list of items and wanted to foreach over them. Of course, in t_sql, you can’t. I could loop over the table if i had that identity column, so i created one.
set @cmd = 'select M.metric, ROW_NUMBER ( ) OVER (order by M.metric ) as RowNum into dbo.myTable from (select distinct metric from server.table.dbo.inputTable ) as M' exec (@cmd) select * from dbo.myTable
As you see, this is a bit of dynamic SQL – i got the distinct metrics in a subquery, and then selected each one with the ROW_NUMBER OVER function.
Then i could loop like this
set @mycounter = @@rowcount set @x = 1 while @x <= @mycounter begin set @met = (select metric from myTable where RowNum = @x) set @cmd = 'some SQL command here;' exec (@cmd) set @x = @x+1 end END
@@rowcount is the number of rows in the myTable, or rather, it is the number of rows which were selected into it, assuming that this code follows the previous. If there are other operations in between, @@rowcount might get reset.