| Author |
 Topic  |
|
|
arkiboys
Flowing Fount of Yak Knowledge
1341 Posts |
 Posted - 07/07/2011 : 09:56:23
|
Cursor 1:
select field1, field2, field3...
from table1, table2, table3, etc...
into @field1, @field2, @field3, ...
Cursor 2:
select field1, field2, field3...
from table15, table20, table35, table25
--UPDATED
WHERE field1 = @field1
etc...
into @chldfield1, @chldfield2, @chldfield3, ...
Cursor2 is within Cursor1
How is it possible to not use these cursors or at least not to use cursor2 for now?
Thank you
|
Edited by - arkiboys on 07/07/2011 10:09:47
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 07/07/2011 : 10:00:16
|
how is cursor 2 related to cursor 1 ?
KH
Time is always against us
|
 |
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 07/07/2011 : 10:01:01
|
Depends a lot on what you are doing with them
There is no dependency here between the 2 cursors so they shouldn't be nested.
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 07/07/2011 : 10:03:18
|
How many posts is this?
You aren't going to get anywhere by asking how to recode cursors.
It's a bit like saying (in fact very like saying) I have an application - how do I improve it?
I could give a nethod to get rid of any explicit cursor but it wouldn't help. It's just replacing the same processing.
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
Edited by - nigelrivett on 07/07/2011 10:04:12 |
|
|
|
arkiboys
Flowing Fount of Yak Knowledge
1341 Posts |
Posted - 07/07/2011 : 10:08:12
|
It is like:
foreach record in cursor1, go through the records in cursor2 and do something like
insert into tblX(Field1, Field2, Field3)
values(123, @chldField1, @Field1)
...
Note that I have updated the initial post to show a where clause.
Thanks |
Edited by - arkiboys on 07/07/2011 10:10:15 |
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 07/07/2011 : 10:13:20
|
something like this
with cte as (<query from cursor1>)
insert tblX(Field1, Field2, Field3)
select 123, a.chldField1, a.Field1
from (<query crom cursor2) a
join cte b
on a.field1 = b.field1
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
Edited by - nigelrivett on 07/07/2011 10:14:15 |
|
|
|
arkiboys
Flowing Fount of Yak Knowledge
1341 Posts |
Posted - 07/07/2011 : 10:20:07
|
This seems to be joining the two datsets.
Am I right? |
|
|
|
arkiboys
Flowing Fount of Yak Knowledge
1341 Posts |
Posted - 07/07/2011 : 10:23:20
|
AND:
How do I check something like:
IF @chldField1 = 'field value in cursor2'
AND @Field1 = 'file value in cursor1'
Thanks |
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 07/07/2011 : 10:30:39
|
That's control of flow - probably changed into a case statement somewhere or maybe part of a join.
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
|
arkiboys
Flowing Fount of Yak Knowledge
1341 Posts |
Posted - 07/07/2011 : 10:34:09
|
quote:
Originally posted by nigelrivett
That's control of flow - probably changed into a case statement somewhere or maybe part of a join.
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Did you see my initial post which I updated with --UPDATED
?, if so, then does your solution in sql still applies the same?
Thanks |
|
|
|
arkiboys
Flowing Fount of Yak Knowledge
1341 Posts |
Posted - 07/07/2011 : 11:04:00
|
quote:
Originally posted by nigelrivett
something like this
with cte as (<query from cursor1>)
insert tblX(Field1, Field2, Field3)
select 123, a.chldField1, a.Field1
from (<query crom cursor2) a
join cte b
on a.field1 = b.field1
This seems to be similar to:
1- placing the result of cursor1 into say #tblcursor1
2- placing the result of cursor2 into say #tblcursor2
3- joining #tblcursor1 and #tblcursor2
via the joined field i.e. field1
Am I right?
Thanks
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
|
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 07/07/2011 : 11:05:24
|
Yes - don't now what it means though.
Usually to rewrite cursors you need to look at what they are doing at a high level not try to replace the detail - that will just end up trying ot do row by row processing which is the underlying issue.
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
| |
Topic |
|
cursors
