473,837 Members | 1,560 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

trying to avoid using cursors!

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?

Jul 23 '05 #1
9 1846

<ia****@yahoo.c om> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.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?


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
Jul 23 '05 #2
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.

Jul 23 '05 #3
On 8 Apr 2005 11:07:12 -0700, ia****@yahoo.co m wrote:
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?


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)
Jul 23 '05 #4
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

Jul 23 '05 #5
On 8 Apr 2005 14:06:59 -0700, ia****@yahoo.co m wrote:
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?


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)
Jul 23 '05 #6
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
--

Jul 23 '05 #7
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.

Jul 23 '05 #8
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 :)

Jul 23 '05 #9
On 8 Apr 2005 19:08:35 -0700, ia****@yahoo.co m wrote:

(snip)
i will learn how to cross-post
before i next have a topic that applies to more than one group.


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)
Jul 23 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

22
10677
by: T.S.Negi | last post by:
Hi All, I want to avoid using cursors and loops in stored procedures. Please suggest alternate solutions with example (if possible). Any suggestion in these regards will be appreciated. Thanks in advance, T.S.Negi
5
2300
by: Mark Harrison | last post by:
In the program testlibpq.c, these five SQL statements are executed: res = PQexec(conn, "BEGIN"); res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from pg_database"); res = PQexec(conn, "FETCH ALL in myportal"); res = PQexec(conn, "CLOSE myportal"); res = PQexec(conn, "END"); Is this just to illustrate how to create transactions and cursors, or is there some material difference between trimming the program down to just:
6
2514
by: a | last post by:
Hello, I am doing some multithreading in an MDI app, and I can't seem to get the cursor to stay as an Hourglass. I call: Cursor.Current = cursors.wait at the beginning of my routing, and set it back to cursors.default when the thread ends (using a callback)
10
17380
by: Just Me | last post by:
Does Me.Cursor.Current=Cursors.WaitCursor set the current property of Me.Cursor to Cursors.WaitCursor And Me.Cursor.Current=Cursors.Default set the Me.Current property to something (default) stored in Me.Cursor. Or is Cursors.Default some process wide cursor shape? What is a correct statement?
5
1387
by: Boni | last post by:
Dear all, 1.Is there a standard set of cursors in windows which can be used? If yes, where. I need a "hand"- cursor for drag-drop operation. 2. What is a best practice to place cursors? In app directory or in resource file? Thanks, Boni
18
2050
by: **Developer** | last post by:
If e.Button = MouseButtons.Left Then also from a Dim Answer As DialogResult = MessageBox.Show.. Select Case Answer Case DialogResult.Yes
3
2305
by: schwartzenberg | last post by:
Dear friends, I have just run into a strange DB2 problem. Something i'd some of you would answer, if only shortly. My basic question is: How do i ensure 'insensitive' (ie static) cursors that are only forward readable (in DB2 for mainframe)?? It seems that the cursors i'm working on suddenly have become (after
5
34264
by: Paul M | last post by:
Hi All, I've been tasked with writing a z/OS C program to read and update a table (DB/2 v8). This Table contains a single row with a single column of data. This program (which will run as a Stored Proc) needs to implement a strategy for concurrency as the Stored Proc can be called concurrently by several different users. The code essentially does the following:
14
5908
by: dba_222 | last post by:
Dear experts, Again, sorry to bother you again with such a seemingly dumb question, but I'm having some really mysterious results here. ie. Create procedure the_test As
0
9843
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9683
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
10630
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10275
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9407
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7811
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7005
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5851
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4476
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.