number the table

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.

Advertisements