Re: [sqr-users] Insert duplicates

["Jamie Harris" <JHarris@frederick.edu>]

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