RE: [sqr-users] Insert duplicates

["Knapp, Richard" <KnappR@umsystem.edu>]

A faster way (one pass) may be to order the select on the key of
interest, store the key in a temp variable, get the next row, compare
the key values and act accordingly, replace the value in the temp
variable, get the next row, etc..  You have to make sure you handle the
first and last rows appropriately in this method.

Richard Knapp
Database Programmer/Analyst
Institutional Research and Planning
University of Missouri System
573-882-8856
knappr@umsystem.edu
-----Original Message-----
From: sqr-users-bounces+knappr=umsystem.edu@sqrug.org
[mailto:sqr-users-bounces+knappr=umsystem.edu@sqrug.org] On Behalf Of
Jamie Harris
Sent: Friday, April 15, 2005 10:54 AM
To: sqr-users@sqrug.org
Subject: Re: [sqr-users] Insert duplicates

 
One sure way to prevent a dupe is to perform a select first and look
for a row matching the keys of what you are about to insert.  
 
This works well if you are inserting rows one at a time - perhaps from
a datafile or something.  
 
Like this:
 
let #found = 0
begin-select
FIELD
   let #found = 1
from TABLE
WHERE (etc.)
end-select
 
if (not #found)
begin-sql
INSERT INTO (etc.)
end-sql
end-if
 
Of course this slows everything down - so be aware of that.
 
-----------------------------------------------------
James Harris
Junior Systems Programmer/Analyst
Information Technology Division
Frederick Community College
-----------------------------------------------------

>>> Murali.Kaithi@spencergifts.com 4/15/2005 11:15 AM >>>

Hi,
I am trying to insert into a table from another table.
If I get across the duplicates, I would like to skip them and insert
rest of the rows.
I remember the below code worked before but is not now, I am confused.
Could someone help me.
My insert statement would start like 

begin-sql ON-ERROR=SQL-Error1

And below is my SQL-Error1 procedure

begin-procedure SQL-Error1
  show '#sql-status is ' #sql-status
  evaluate #sql-status
#ifdef DB2
    when = 6100    !DB2 error for empty-table result set
      break
#end-if

#ifdef DB2UNIX
    when = 6100    !DB2 error for empty-table result set
      break
#end-if

    when = -99999  !Token "when" clause for non-DB2 environments
    when = -9      !unique constraint for Oracle Environments
    when-other
      display $sqr-program noline
      display ': ' noline
      display $ReportID noline
      display ' - SQL Statement = '
      display $SQL-STATEMENT
      display 'SQL Status =' noline
      display #sql-status 99999 noline
      display ' ' noline
      display 'SQL Error  = ' noline
      display $sql-error
      display $Sql-Msg
   SHOW  $loadrecord
      Do Rollback-Transaction
      stop
  end-evaluate
end-procedure SQL-Error1

Thanks,
Murali


______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
______________________________________________________________________

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users