Help me avoid a cursor - SQL Server Forums

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

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help me avoid a cursor

Author

Topic

smccreadie
Aged Yak Warrior

505 Posts

Posted - 2002-05-23 : 09:39:21

I've got data coming into SQL once a day (~500 rows). If a row in the data meets certain criteria, I need to do the following actions to that row (there are multiple criteria that need to be looped):

1. Insert some values into 2 different tables and return 2 @@identity values
2. Insert the original data (joined to some other data) with the @@identity values returned in step 1.

My first thought is to use a cursor, but I'd rather not. Any ideas how I can examine the rows against the first criteria. If it meets, then do the 2 steps. Then I need to go back through the rows the second, third, etc. criteria to do the same.

The amount of data is small enough that I'm not too worried about performance.

Thanks

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-05-23 : 10:47:00

Hmm, Page47 should be all over this question in his tireless pursuit of cursors. But lemme try

We're gonna use a trigger.

create trigger on tablename for insert
as
-- If there is some criteria for weather or not you insert into another table, use something like 'if select data from inserted'
insert into tablename2(columns) values(select columns from inserted)

Hard to give a full answer without knowing what the names of the columns or tables are... But a trigger can do this for ya

smccreadie
Aged Yak Warrior

505 Posts

Posted - 2002-05-23 : 11:39:32

I don't think a trigger is going to work because the data comes as a batch and triggers execute per statement, not per row.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-23 : 11:58:37

True, but instead of processing each row, you hit the whole batch (which is what you were asking for) If you need to manipulate newly inserted rows, you can get them from the inserted table and put them into a worktable (may not be able to use a temp table, will have to test it) You would add only those rows that match the criteria you need, and you'd grab them all at once.

Can you list the criteria you need to match and whatever other actions you need to do? It's hard to figure out without more specifics. Please include EVERYTHING no matter how long and involved it might be.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-23 : 12:00:36

I am assuming the @@identity values are the PKs for the 'other' tables and like for example if you have a lookup table called gender and it has 1,male and 2,female and someone comes along and calls their gender yes, you want to go insert yes into gender and capture the id = 3 to put into your table as a foreign key . . .

assuming that is the type of thing you are doing . . . I would pump the data raw into a staging table. then go insert into your lookup tables these new/missing values (don't worry about the ids). once you have all the lookup tables ready to accept the new data, insert into your final table from the staging table inner joined to all your lookup tables.

like . . . using my example


insert gender
select
    gender
from
    stagingtable s
where
    not exists (
        select 1
        from
            gender
        where
            gendertype = s.gender

-- do this insert...select...where not exists for each of your rules

insert finaltable (<collist>)
select
    <collist>,
    g.genderid
from
    stagingtable s
    inner join gender g
        on s.gender = g.gender

Am, I may be off base with what you are trying to do.

<O>

smccreadie
Aged Yak Warrior

505 Posts

Posted - 2002-05-23 : 20:47:18

Let me try to explain more. I'd love to post the code except it isn't written yet.

I've got a table of specialty medication orders for a patient. These rows have a number of columns (drug_name, code, medical_service, patient_location, etc.). The project requires that pharmacists retrospectively document that these orders met established critera or the criteria was bypassed. There is a web gui for this and it works but compliance isn't all that great. The user picks the order, assigns the correct code to it and submits it. The data is written to three tables overall.

Documentation will be better if the server could do some of the assignment of the correct code. For some orders, the criteria is objective (i.e. patient is > 50 yrs) and the computer can assign.

What I'm trying to do is loop through the orders in the system (~500 that come across via batch). The process would need to do the following:

For criteria #1
1. If the order met the criteria (i.e. drug is tylenol and patient > 50)
a. assign the correct classification (through a join to the criteria table)
b. Insert part of the data into a security shell table (returns the @@IDENTITY)
c. Inserts the patient demographic data into another table (returns the @@IDENTITY)
d. Inserts the rest of the data into the final table complete with coding
2. Check the next row
3. Loop until criteria #1 no longer met

For criteria2 (drug is motrin and medical_service = 'rrr')
1. see above.....

I've been playing a little with a WHILE loop but not sure if this is the right way to go.

Any ideas?

Edited by - smccreadie on 05/23/2002 20:49:21

Edited by - smccreadie on 05/23/2002 20:50:14

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-23 : 21:17:31

quote:
For criteria #1
1. If the order met the criteria (i.e. drug is tylenol and patient > 50)

This part, as you know, is pretty simple. You can probably make all of your criteria into one decent where clause. Or, alternativily, you could process each criteria individually, in which case the where clause becomes that much simpler....

quote:
a. assign the correct classification (through a join to the criteria table)

Like you say, should be a simple join...

quote:
d. Inserts the rest of the data into the final table complete with coding

(out of order), but this is simple your column list in the select.

quote:
b. Insert part of the data into a security shell table (returns the @@IDENTITY)
c. Inserts the patient demographic data into another table (returns the @@IDENTITY)

Now this is the tricky part. However, I think I still stand by original suggestion. Insert into your 'security shell' and 'demographic' tables records which do not already exists. Then when you construct this set-based single select statement, you will simply join back to these tables to get the ids. Now I certainly don't understand the data or the nature of you objects based soley on the naming (security shell?...like a turtle?

), but from where I stand I think you should be able to get this to work.

So I see something like this.


insert security_shell (<col_list>)
select <security shell columns?>
from <startingtable> st
where not exists ( select 1
                   from security_shell
                   where <col> = st.<col> )

insert demograph (<col_list>)
select <demographic columns?>
from <startingtable> st
where not exists ( select 1
                   from demograph
                   where <col> = st.<col> )

insert final_table
select
    s.securityshellid,
    d.demographicid,
    st.<other_columns>
from
    <startingtable> st
    inner join security_shell s
       on st.<col> = s.<col>
    inner join demograph d
       on st.<col> = d.<col>
where
    (drug is tylenol and patient > 50) or
    (drug is motrin and medical_service = 'rrr') or
    <etc.etc.>

<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources