Newbie CURSOR question, insert values into a table? -- sql server (alternate)

sql NO[at]SPAM hayes.ch
7/4/2003 2:36:29 AM

You can do this, but in general you should avoid cursors unless there
is no other choice. It may be that your cursor can be written as a
single insert statement, in which case it will be a lot faster and
easier to code. See below for a rough example.

Simon

/* With a cursor */

declare @var1 int, @var2 int

declare cur cursor fast_forward for
select col1, col2
from dbo.table1

open cur

fetch cur into @var1, @var2

while @@fetch_status = 0
begin
if @var1 > @var2 -- check conditions
begin
insert into dbo.table2 (col1, col2)
select @var1, @var2
end

fetch cur into @var1, @var2
end

close cur
deallocate cur

/* With an insert */

insert into dbo.table2 (col1, col2)
select col1, col2
from dbo.table1
where col1 > col2

[quoted text, click to view]

amitb NO[at]SPAM zenithinfotech.com
7/4/2003 2:49:28 AM

Hi,

Refer solution below:

Entity/Objects:
(Assume all fields in this example are varchar).

Table1 (Field1,Field2)
Table2 (Field1,Field2, Field3)

Script:
Declare @F1 Varchar, @F2 Varchar

--Get records
Declare RS cursor for Select Field1, Field2 from Table1 Where Field1=
‘A%'
Open RS --Open cursor

Fetch Next From RS into @F1, @F2
While @@Fetch_Status=0 --Check cursor if end of file
Begin
--Your insert in second table if condition matches.
If RTrim(@F1) = ‘ABC' And RTrim(@F2) = ‘Google'
Begin
--Post script to insert in Table2
Insert Into Table2 ……

End

Fetch Next From RS into @F1, @F2
End

Close RS --Close cursor
Deallocate RS --relaese memory.

For additional information on updating selected columns in cursor and
complete syntax refer SQL Server Books Online sections.
a)DECLARE CURSOR
b)@@Fetch_Status

Hope this helps you.
Thanks Amit

[quoted text, click to view]

Henrik Hjøllund Hansen
7/4/2003 8:20:34 AM

How can I insert values into a table based on the fetch I do. The examples
I have been able to find all shows how to use "print" to show how Cursors
work.

I want to read through a table, and only insert values into another table if
some conditions on the row is true.

Can this be done, if so how?

Thanks in advance.

Henrik.

Henrik Hjøllund Hansen
7/4/2003 12:02:08 PM

Thanks a lot for your answers.

Henrik

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server (alternate)

sql server (alternate) : Newbie CURSOR question, insert values into a table?