fetch value cursor to table

mateenmohd · August 2nd, 2004, 05:00 AM

following procedure retrieve values from the table and print in query analyzer by the cursor.

how can store (save) cursor values into table ?

I want that few columns data move to another table by the procedure and query ?

CREATE PROCEDURE [dbo].[customer] AS
declare @custid int
declare @custname varchar(30)
declare custcursor cursor for
select proj_id,proj_name from archive order by proj_id
for read only
open custcursor
while (0 = 0 ) begin
fetch next
from custcursor
into @custid,@custname
if (@@fetch_status <> 0 ) break

print cast (@custid as varchar (10) ) + ' ' + @custname

/* here it also save the column data to another table
   create table abc
   (proj_id int, proj_name nvarchar(50))

   insert into abc(proj_id,proj_name) values(@proj_id,@proj_name)
   (fetch values from cusor to table abc, how ?)
*/

end
close custcursor
deallocate custcursor
GO

regards.

Mateen

happygv · August 2nd, 2004, 05:53 AM

Hi,

Code:

CREATE PROCEDURE [dbo].[customer] AS
declare @proj_id int
declare @proj_name varchar(30)
Do a check and Create table only if it NOT exists
create table abc (proj_id int, proj_name nvarchar(50))
declare custcursor cursor for
select proj_id, proj_name from archive order by proj_id
for read only
open custcursor
fetch next from custcursor into @proj_id, @proj_name
while (@@fetch_status = 0) 
begin
   print cast (@proj_id as varchar (10) ) + '  ' + @proj_name
   insert into abc(proj_id, proj_name) values(@proj_id,@proj_name) 
   fetch next from custcursor into @proj_id, @proj_name
end
close custcursor
deallocate custcursor 
GO

You seem to have confused with the variable usage within the cursor.

You can do it this way. But I really wonder why you have do that within cursor for every row, instead of doing it in a simpler way that exists. It is easier to do that without using cursor as given below.

Code:

INSERT INTO abc (select proj_id, proj_name from archive order by proj_id)

Cursor is best fit for doing something like eg: calculating/updating values row by row and/or displaying it a formatted manner, not for inserting row by row into another table, which can be done at one shot in a easier way.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

mateenmohd · August 2nd, 2004, 06:41 AM

Thanks.

I want to confirm that how cursor (temporary store) work to save data
into physical store.
it working is very slow.

it is easier ways to user insert into statement....

regards.

Mateen