Suggested methodology for SP update 2

[MarkButler]

I have read a number of times in this forum about the use and expense of using a cursor in a stored procedure. With that in mind I was wondering how one might best accomplish the following without using a cursor.

Select all records from a table which have a status of open. If open and the due date is today, or less than today, create a record in another table (reminder) if it does not already exist. If it exists update it with appropriate information.

My initial take would have been to do the select and loop through the cursor seeing if I need to update the other table (reminder), and if so, issue a read to see if it already exists and either do an update or insert.

What would be the better solution?

TIA

Mark

 

[markVII]

Use the forward only cursor, as I recommended in the previous thread.

 

[ArtieChoke]

Assuming you have unique records (in both tables), then first do an update:

update table2
set ...
from table1 t1, table2 t2
where t1.key = t2.key
and t1.status = 'open'
and t1.duedate <= getdate()

Then insert any missing entries (this assumes both tables have the same structure):

insert into table2
select * from table1
where key not in (select key from table2)


"I think we're all Bozos on this bus!" - Firesign Theatre

 

[nigelrivett]

For the second query I would

insert into table2
select table1.*
from table1
left join table2
on table2.key = table1.key
where table2.ket is null


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[MarkButler]

First thanx to ArtieChoke and NigelRivett. The following is what I came up with based on your suggestions. BTW this previously was being done in the client application and as the databases grew, so did the time it took to perform these actions. The following update and insert is only one of 14 similar actions and took seconds instead of minutes to perform.

The following UPDATE is working as it is supposed to. I only show it here for anyone else following this tread. The insert is giving me some problems though.

   -- first lets take care of updates...
   UPDATE diary SET
         di_user = c9.c9_nurse,
         di_task_dt = c9.c9_due_dt,
         ...
         di_chg_in = 'SQL'
      FROM
         c9, diary
      WHERE
         c9.c9_9_docu = LEFT(diary.di_dest_pk,20) AND
         c9.c9_due_fl = 'D' AND
         c9.c9_due_dt < @a_date

The diary table has an identity field which is programatically created in the application. Not something I can change. So what I was going to do was to create my own identity field which was derived differently and would not collide with the application. I thought about getting the highest used program generated identity and use it as a seed.

   -- get highest assigned identity...
   SELECT @a_seed = MAX(di_pk) FROM diary

The problem is that you can't use the IDENTITY function except in an insert. Can the following be restructured to allow the use of the IDENTITY function or is there a better way?

      -- now take care of inserts...
      INSERT INTO diary
         SELECT
               IDENTITY(int, @a_seed, 1),
               c9.c9_nurse,
               'A',
               c9.c9_due_dt,
               ...
               'SQL'
            FROM
               c9
               LEFT JOIN diary ON LEFT(diary.di_dest_pk,20) = c9.c9_9_docu
            WHERE
               diary.di_dest_pk IS NULL AND
               c9.c9_due_fl = 'D' AND
               c9.c9_due_dt < @a_date

Again thanx for the suggestions and help thus far.

Mark

 

[ArtieChoke]

If you mean the field has the identity attribute set in the db, then you can't set it's value - the database handles that auto-magically . Just leave out that field value and the insert will work.

Another option is to use a select into statement, but the database will need that option set on (bulk/insert okay).

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[MarkButler]

The first field I should have said was, "like an identity field" in that it is an integer field that is incremented by the application using an applications next available number type table. The field attribute is not set as "IDENTITY".

I do need to insert this value and it must be unique. The problem I have is that the "IDENTITY()" function must be used in an "INSERT" statement and the way the query is formed it is part of the "SELECT".

Because the value must be determined in the "INSERT/SELECT" statement I do not have control to be able to just increment a counter and use it. My original intent was to find the highest used value and use that as a starting point and let the "IDENTITY()" function increment the value.

Is there another function that could be used? Is this a place to use a UDF?

Mark

 

[ArtieChoke]

Maybe something like this will work (from TSQL help):

SELECT emp_id AS emp_num,
fname AS first,
minit AS middle,
lname AS last,
IDENTITY(smallint, 100, 1) AS job_num,
job_lvl AS job_level,
pub_id,
hire_date
INTO employees
FROM employee


"I think we're all Bozos on this bus!" - Firesign Theatre

 

[nigelrivett]

Not sure what you are trying to do.
If you just want a unique value then just make the column an identity and omit it from the insert - it will be incremented automatically.
If you want it to start from 1 for each batch then

select identity(int,1,1) as id, ....
into #a
from .......

insert diary
select id, ...
from #a


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[snag56]

How about:

begin transaction

UPDATE reminder
SET reminder.mycolumn = SOT.mycolumn, ....
FROM reminder
INNER JOIN someothertable SOT
ON reminder.matchup_col1 = SOT.matchup_col1
AND reminder.matchup_col2 = SOT.matchup_col2
...
where SOT.status = 'open'

INSERT INTO reminder(mycolumn, ....)
SELECT SOT.mycolumn,
....
FROM someothertable SOT
LEFT OUTER JOIN reminder R
ON R.matchup_col1 = SOT.matchup_col1
AND R.matchup_col2 = SOT.matchup_col2
where SOT.status = 'open'
and R.matchup_col1 is null
AND R.matchup_col2 is null
and SOT.matchup_col1 is not null
AND SOT.matchup_col2 is not null
ORDER BY SOT.mycolumn, ....

commit transaction