Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

arkiboys
Flowing Fount of Yak Knowledge

1433 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
17689 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

1433 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

1433 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

1433 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

1433 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

1433 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  
 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.08 seconds. Powered By: Snitz Forums 2000