SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 cursors
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

arkiboys
Flowing Fount of Yak Knowledge

1406 Posts

Posted - 07/07/2011 :  09:56:23  Show Profile  Reply with Quote
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
17595 Posts

Posted - 07/07/2011 :  10:00:16  Show Profile  Reply with Quote
how is cursor 2 related to cursor 1 ?


KH
Time is always against us

Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 07/07/2011 :  10:01:01  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 07/07/2011 :  10:03:18  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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
Go to Top of Page

arkiboys
Flowing Fount of Yak Knowledge

1406 Posts

Posted - 07/07/2011 :  10:08:12  Show Profile  Reply with Quote
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
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 07/07/2011 :  10:13:20  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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
Go to Top of Page

arkiboys
Flowing Fount of Yak Knowledge

1406 Posts

Posted - 07/07/2011 :  10:20:07  Show Profile  Reply with Quote
This seems to be joining the two datsets.
Am I right?
Go to Top of Page

arkiboys
Flowing Fount of Yak Knowledge

1406 Posts

Posted - 07/07/2011 :  10:23:20  Show Profile  Reply with Quote
AND:
How do I check something like:
IF @chldField1 = 'field value in cursor2'
AND @Field1 = 'file value in cursor1'

Thanks
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 07/07/2011 :  10:30:39  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

arkiboys
Flowing Fount of Yak Knowledge

1406 Posts

Posted - 07/07/2011 :  10:34:09  Show Profile  Reply with Quote
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
Go to Top of Page

arkiboys
Flowing Fount of Yak Knowledge

1406 Posts

Posted - 07/07/2011 :  11:04:00  Show Profile  Reply with Quote
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.

Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 07/07/2011 :  11:05:24  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000