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.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
"Henrik Hjllund Hansen" <hh@.dlf.dk> wrote in message news:<be367e$2fng$1@.news.cybercity.dk>...
> 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.|||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
"Henrik Hjllund Hansen" <hh@.dlf.dk> wrote in message news:<be367e$2fng$1@.news.cybercity.dk>...
> 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.|||Thanks a lot for your answers.
Henrik
"Henrik Hjllund Hansen" <hh@.dlf.dk> wrote in message
news:be367e$2fng$1@.news.cybercity.dk...
> 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.
