Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 2nd, 2004, 05:00 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default fetch value cursor to table


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


 
Old August 2nd, 2004, 05:53 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old August 2nd, 2004, 06:41 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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







Similar Threads
Thread Thread Starter Forum Replies Last Post
Magnetic Cursor - Target Area Cursor? gcarcass .NET Framework 2.0 1 May 5th, 2008 07:20 AM
Fetch Data From server ashvinm JSP Basics 2 March 10th, 2008 07:48 AM
how to fetch records from website avats ADO.NET 0 November 3rd, 2005 03:45 PM
Nested Fetch statement cole SQL Language 0 May 11th, 2005 06:47 PM
fetch in background? RCC_msolomon ADO.NET 6 June 25th, 2003 10:39 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.