trying to avoid using cursors!

[iaesun@yahoo.com]

i'm trying to write a batch that will perform a complex task using
set-based selects instead of a row-based cursor. let me know if you can
help me figure out how.

description of what i'm trying to do:

there is TABLE1, TABLE2, and TABLE3

i want to select each row from TABLE1, do some analysis on the data of
that row, and then perform an insert of some data into TABLE2, and some
data into TABLE3

how do i do this in a T-SQL batch?

[Simon Hayes]

Re: trying to avoid using cursors!


<iaesun@yahoo.c om> wrote in message
news:1112983632 .613978.231800@ f14g2000cwb.goo glegroups.com.. .[color=blue]
> i'm trying to write a batch that will perform a complex task using
> set-based selects instead of a row-based cursor. let me know if you can
> help me figure out how.
>
> description of what i'm trying to do:
>
> there is TABLE1, TABLE2, and TABLE3
>
> i want to select each row from TABLE1, do some analysis on the data of
> that row, and then perform an insert of some data into TABLE2, and some
> data into TABLE3
>
> how do i do this in a T-SQL batch?
>[/color]

It all depends on what your "analysis" logic is doing - it may be
convertible to a set-based solution or not. Without some sample code and
more explanation, all I can really suggest is looking at breaking down the
cursor logic into steps, and seeing if some parts can be converted into
views or functions.

http://www.aspfaq.com/etiquette.asp?id=5006

Simon

[iaesun@yahoo.com]

Re: trying to avoid using cursors!

well, i left the analysis out because it is fairly arbitrary. for
example: how would i accomplish what i described if there were no
analysis at all? just looping through each row of TABLE1 and inserting
some data into TABLE2 and some data into TABLE3?

but! as for the hypothetical analysis, it might look something like
this chunk of psuedo-code:

for each ROW of TABLE1
{
if not exists (select * from TABLE2 where COLUMNA = ROW.COLUMN1)
insert into TABLE2 (COLUMNA) values (ROW.COLUMN1)

set @table2id = select ID from TABLE2 where COLUMNA = ROW.COLUMN1
insert into TABLE3 values (ROW.COLUMN2, ROW.COLUMN3, @table2id)
}

forgive the psuedo-coded "for each row" .. that's the part i'm not sure
how to do in a set-based solution.

does that make sense? for each row in the first table, i might want to
check for the existance of a value in the second table, and if it's not
there, insert it. then select a value from that matching row, to insert
some more rows form the first table, plus the id of the row from the
second table, into a third table.

that general sort of thing.

[Hugo Kornelis]

Re: trying to avoid using cursors!

On 8 Apr 2005 11:07:12 -0700, iaesun@yahoo.co m wrote:
[color=blue]
>i'm trying to write a batch that will perform a complex task using
>set-based selects instead of a row-based cursor. let me know if you can
>help me figure out how.
>
>description of what i'm trying to do:
>
>there is TABLE1, TABLE2, and TABLE3
>
>i want to select each row from TABLE1, do some analysis on the data of
>that row, and then perform an insert of some data into TABLE2, and some
>data into TABLE3
>
>how do i do this in a T-SQL batch?[/color]

Hi iaesun,

If I'm not mistaken, you've already received several replies in
microsoft.publi c.sqlserver.pro gramming.

Please don't post the same question too multiple newsgroups!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[iaesun@yahoo.com]

Re: trying to avoid using cursors!

even though the groups cover identical topics, and might have very
different audiences?

allow me to demonstrate with an analogy:

if a city has two newspapers, and you have news about a particular
person in the city, would it not be wise to publish an article in each
newspaper to ensure it reaches the appropriate audience?

i didn't decide that there should be two identical newsgroups for this
particular topic, but it seems reasonable to me to publish a topic to
any groups where that news would be relevant. is that misguided?

sincerely,

jason

[Hugo Kornelis]

Re: trying to avoid using cursors!

On 8 Apr 2005 14:06:59 -0700, iaesun@yahoo.co m wrote:
[color=blue]
>even though the groups cover identical topics, and might have very
>different audiences?
>
>allow me to demonstrate with an analogy:
>
>if a city has two newspapers, and you have news about a particular
>person in the city, would it not be wise to publish an article in each
>newspaper to ensure it reaches the appropriate audience?
>
>i didn't decide that there should be two identical newsgroups for this
>particular topic, but it seems reasonable to me to publish a topic to
>any groups where that news would be relevant. is that misguided?[/color]

Hi jason,

Your analogy is flawed. If I read a newspaper, I don't spend time trying
to figure out a solution. If I read a newsgroup, I do - and if I later
find that the same question is already answered in another group, then I
have wasted time that I could have used helping others.

If you really feal that you must post to multiple groups, then at least
use cross-posting instead of multiposting. That results in all answers
being directed to all groups as well (unless the replier tampers with
the settings, but that's out of your control).

More about this is here: http://www.aspfaq.com/etiquette.asp?id=5003
(Though in spite of what it says on that page, I am *not* annoyed that I
had to read your question again - but I am annoyed that I've spent time
trying to find a solution that you already have been given by someone
else).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[David Portas]

Re: trying to avoid using cursors!

This is called multi-posting and is considered a breach of newsgroup
etiquette. The reason is that others may waste time responding with
points that have already been made in another group, thus making the
whole experience less productive for everyone.

If you aren't sure which group to post it's better to *cross-post* to
one or two relevant groups. Cross-posting means that the same message
is copied to more than one group but there is still only ONE thread
that everyone sees, so the discussion is shared. Unnecesary
cross-posting is also frowned on, but moderate cross-posting when you
aren't sure of the right group is tolerated in most cases, unlike
multi-posting, which is just considered bad manners.

The newspaper analogy is not a good one because a newspaper is not an
interactive medium.

--
David Portas
SQL Server MVP
--

[iaesun@yahoo.com]

Re: trying to avoid using cursors!

actually, the newspaper analogy is improved if i find a lost dog and
want to post a classified ad to find the owner. that IS interactive,
and does make my point more clearly.

however! i do not disagree with this "cross-posting" that you mention.
if i could "cross-post" a classified ad about a lost dog in more than
one metro newspaper, that would be clearly superior as well.

i will be sure to figure out how to cross-post before i next post
something that applies to more than one newsgroup.

[iaesun@yahoo.com]

Re: trying to avoid using cursors!

ahhh, i had not heard of cross-posting. now i have. in a world without
cross-posting, i think my newspaper analogy is sound :) especially with
the "you have a lost dog and want to publish a classified ad for the
owner to read" improvement that i later thought of.

however, i don't disagree with cross-posting. even newspaper classified
ads would be improved by such a feature. i will learn how to cross-post
before i next have a topic that applies to more than one group.

as someone who is only a casual user of this, i would recommend that
you mention cross-posting to future multi-posters, instead of just
telling them not to multi-post when that is a reasonable course of
action if you don't know about cross-posting :)

[Hugo Kornelis]

Re: trying to avoid using cursors!

On 8 Apr 2005 19:08:35 -0700, iaesun@yahoo.co m wrote:

(snip)[color=blue]
>i will learn how to cross-post
>before i next have a topic that applies to more than one group.[/color]

Hi Jason,

Your message's headers show that you are posting through Google. That
interface has a very simple way to create cross-posts: simply type in a
comma-seperated list of all groups you want to post to.

Do keep in mind that excessice cross-posting is also frowned upon.
Posting to one group is preferred; cross-posting to two, or at most
three groups if the subject matter falls between two (or three) stool,
is acceptable; cross-posting to all SQL Server related groups is not.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)