If (and only if) you have no control over the structure of the database, it's a 2-step procedure. Query the table for records that match (select * where field='no duplicates'). If you get no results, then insert the record. And be wary of race conditions if the db is multiuser!

Some tips:
You can replace:

while ($count<=@IDnumber) {
  if ($match == $IDnumber[$count]){
    goto NEXTLINE1;
  }
  $count++;
}
[download]

next if(grep {$_ eq $match} @IDnumber);
[download]

As to the IDs, first, you should probably check the current values in the table, to see if the one you want to insert is present, assuming there are any. But a much easier way to do this would be to make the field in the table unique ('no duplicates', in Access), in which case it will simply refuse to insert that line, and you can carry on to the next.

C.

Hi there, thank you for your advice. I really learned from what you wrote. But I still have some prolem. When I was trying to set the Access table to refuse duplicate values, the program simply stopped and gave error message when there were duplicated values. So I want to first read what are already in the table into an array, lets say array A1, and compare the values I intent to insert into the table with the values in A1. If a value has already been in A1, I will discard it and go to the next value. If the next value is not in A1, I will put it into an array A2. After this process ends, I then will insert the values in A2 into the Access table. My problem is that the values in A2 contains some values in A1. I do not know why.

Here is the line that is exiting your script:

die qq(SQL fail "$rc":), $db->Error(),qq(n) if $rc;

Just handle the error differently. Check $rc or $db->Error(), and continue with the next insert if the error points to a duplicate value.

# untested
if ($rc == WHATEVER_ERROR_IS_GENERATED_BY_DUPLICATE) {
    # you can also test ($db->Error() =~ /some value/)
    next;
} else {
    die qq(SQL fail "$rc":), $db->Error(),qq(n);
}
[download]

Your script will still die if the insert fails for a different reason.

HTH...

Access has a query wizard to find duplicate values in a table.
First, add an autoincrement column to the table like others suggested.
Second, run the Find Duplicates query wizard to generate a query that finds all the duplicate rows.

Use that  Find Duplicates query as the basis to generate and fine tune a  Delete query that gets rid of the duplicate rows by eliminating the rows having an autoincrement value higher the lowest one found for each duplicate.

Then delete the autoincrement column from the table and then re-add it again. This should work as long as you haven't used the column or table in a Relationship.

Now you should have consecutively numbered rows with clean nonduplicated data in a table that generates it's unique row autocount ID automatically.

Now to your code.

• When you're looking for a number like that, you don't need to use $&, simply enclose what you're looking for in ().
• You don't need $', because Perl will keep track of where it was in the string.
• You don't need to scan continously over an array to see if a number is there, that's what hashes are for.
• You don't need the GOTO, because Perl provides the next keyword.

while ($CONTENDS=~/(\d{10})/g) {
    $match=$1;
    $IDNUMBER{$match}=1 unless $IDNUMBER{$match}; 
}

foreach $match (keys %IDNUMBER) {
  $sqlinsert="INSERT INTO $tab[$loop] VALUES('$match')";
                 $rc=$db->Sql($sqlinsert);
                die qq(SQL fail "$rc":), $db->Error(),qq(n) if $rc;
}
[download]

The reason why you should try to avoid $` and use capturing parentheses in regexes is that it really slows down your regex processing.

The same goes for $´ and (but to a lesser degree) $&.

Once you use them anywhere in your script all your regex-processing slows down, even those who do not use these variables.

CountZero

"If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

Thank you so much. I am a starter in Perl. What you said help me a lot.....Luoina