CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008
James
James

Reputation: 2246

Iterate through a temp table

I did try looping through a temp table, but of no use end up with so many errors only.

Please help me on how can I work this one out..

My need is like :

Please guide me on how to do this.

I did search a lot..and got confused with cursor and set based join.

Upvotes: 1

Views: 7865

Answers (1)

peterm
peterm

Reputation: 92845

IMHO in your case you don't need a temp table.

Let's say you have following two tables. First is source table and the other is destination table.

CREATE TABLE mytable(id INT IDENTITY PRIMARY KEY, col1 nvarchar(10));
CREATE TABLE mytable2(id INT, value INT);

mytable has following sample data in it:

id          col1
----------------
1           row1      
2           row2      
3           row3      
4           row4

And you have some SP that calculates something based on id, something like this

CREATE PROCEDURE getValue (@id INT, @value INT OUT) 
AS
 SET NOCOUNT ON;
 SET @value = @id * 2;

Then you can create an SP that will do the job like this

CREATE PROCEDURE proccessData
AS
  SET NOCOUNT ON;
  DECLARE @id INT, @value INT;
  DECLARE id_cursor CURSOR FOR 
   SELECT id FROM mytable WHERE id > 1 AND id < 4;

  OPEN id_cursor;

  FETCH NEXT FROM id_cursor INTO @id;
  WHILE @@FETCH_STATUS = 0
  BEGIN
    EXEC getValue @id, @value OUTPUT;
    INSERT INTO mytable2 VALUES(@id, @value);
    -- Or use update if you already have id's in mytable2
    -- UPDATE mytable2 SET value = @value WHERE id = @id;
    FETCH NEXT FROM id_cursor INTO @id;
  END
  CLOSE id_cursor;
  DEALLOCATE id_cursor;

After you call that SP 

EXEC proccessData

you'll get in mytable2

id          value
----------- -----------
2           4
3           6

And here is working sqlfiddle

Upvotes: 1

Related Questions