Ignore errors inserting dup records?

[pt]

I have a table "T" with an autonum primary key field "ID" and a
text/string field "v". The field "v" is indexed and declared UNIQUE.
Is there a syntax which will allow me to add records and ignore (or
not add) any which would violate the "unique" aspect of "v"?

Table "T":
ID v
----
1 I
2 am
3 the
4 walrus
5 oo
6 koo
7 katchoo

Given this table, I run this :
INSERT INTO T (v) VALUES ("The" , "walrus" , "was" , "Paul") ;

The insertion of "walrus" will fail because it already exists, and "v"
is indexed/no_dups. I want the SQL to insert records for "The",
"was", and "Paul", and not error when in sees "walrus" already present
at record #4.

I'm doing this with Perl + DBI/DBD (not ODBC) talking to a MS-Access
..MDB file.

Thanks in advance.

 

[Kevin Collins]

I have a table "T" with an autonum primary key field "ID" and a
text/string field "v". The field "v" is indexed and declared UNIQUE.
Is there a syntax which will allow me to add records and ignore (or
not add) any which would violate the "unique" aspect of "v"?

Table "T":
ID v
----
1 I
2 am
3 the
4 walrus
5 oo
6 koo
7 katchoo

Given this table, I run this :
INSERT INTO T (v) VALUES ("The" , "walrus" , "was" , "Paul") ;

The insertion of "walrus" will fail because it already exists, and "v"
is indexed/no_dups. I want the SQL to insert records for "The",
"was", and "Paul", and not error when in sees "walrus" already present
at record #4.

No, it won't. It will fail because you are trying to insert 4 column values
into a table which has oonly 1 column!

 

[Tore Aursand]

Table "T":
ID v
----
1 I
2 am
3 the
4 walrus
5 oo
6 koo
7 katchoo

Given this table, I run this :
INSERT INTO T (v) VALUES ("The" , "walrus" , "was" , "Paul") ;

The insertion of "walrus" will fail because it already exists, and "v"
is indexed/no_dups.

No. The insertion will fail because the SQL statement is invalid; You're
trying to insert data into 4 columns, but you only specify one columns to
insert into.

You probably want to do this:

INSERT INTO T (v) VALUES ('The'), ('walrus'), ('was'), ('Paul')

The above is a multiline insert which will work fine, and if you have
defined the 'v' column to be unique, it'll be impossible to insert any
duplicate data into it.

In Perl this could be written something like this;

my @data = qw( The walrus was Paul );
my $values = join( ',', ('(?)') x @data );

my $sth = $dbh->prepare( 'INSERT INTO T (v) VALUES ' . $values );
$sth->execute( @values );
$sth->finish();

I'm doing this with Perl + DBI/DBD (not ODBC) talking to a MS-Access
.MDB file.

While we're at it: Consider a) calling your table and columns something
different and b) changing database backend.

 

[pt]

No, it won't. It will fail because you are trying to insert 4 column values
into a table which has oonly 1 column!

You noticed that SQL isn't my strong suite. Are you saying the only
way to insert 4 rows is to execute 4 individual SQL statements?
Sounds like a lot of overhead. Is there another way? What if I have
not 4, but 40,000 records I want to insert?

 

[Matthew Braid]

I have a table "T" with an autonum primary key field "ID" and a
text/string field "v". The field "v" is indexed and declared UNIQUE.
Is there a syntax which will allow me to add records and ignore (or
not add) any which would violate the "unique" aspect of "v"?

Table "T":
ID v
----
1 I
2 am
3 the
4 walrus
5 oo
6 koo
7 katchoo

Given this table, I run this :
INSERT INTO T (v) VALUES ("The" , "walrus" , "was" , "Paul") ;

The insertion of "walrus" will fail because it already exists, and "v"
is indexed/no_dups. I want the SQL to insert records for "The",
"was", and "Paul", and not error when in sees "walrus" already present
at record #4.

I'm doing this with Perl + DBI/DBD (not ODBC) talking to a MS-Access
.MDB file.

Thanks in advance.

I'll assume you mean ("The"), ("walrus"), ("was"), ("Paul")...

You can wrap your statement handle execute calls in an eval and just not care if
it fails, or if access supports it (and it probably doesn't going by how truly
horrible access is) you can use INSERT IGNORE instead of INSERT. With mysql at
least this will repress duplicate key errors (turns a duplicate insert into a noop).

MB

 

[Tore Aursand]

You noticed that SQL isn't my strong suite. Are you saying the only way
to insert 4 rows is to execute 4 individual SQL statements? Sounds like
a lot of overhead. Is there another way? What if I have not 4, but
40,000 records I want to insert?

You could take a look at my example; I think it's the fastest way to solve
multi-inserts, though there are other (though slower options);

my $sth = $dbh->prepare( ... );
while ( ... ) {
$sth->execute( ... );
}
$sth->finish();

The example above prepares the SQL statement only once (which saves some
time) and then inserts the data via the execute statement using binded
variables. Take a look at the DBI documentation for more information.

The solution above is still a lot slower than multi-line inserts, though,
so if you need full speed you should stick with that.

 

[Tore Aursand]

You could take a look at my example; I think it's the fastest way to
solve multi-inserts, though there are other (though slower options);

my $sth = $dbh->prepare( ... );
while ( ... ) {
$sth->execute( ... );
}
$sth->finish();

I see that I could be mistaken here; When I refer to "my example" above, I
do _not_ mean the example in the same post (ie. the code above), but the
example I gave in another post;

<[email protected]>


--
Tore Aursand <[email protected]>
"Have you ever had a dream, Neo, that you were so sure was real? What
if you were unable to wake from that dream? How would you know the
difference between the dream world and the real world?" (Morpheus, The
Matrix)

 

[Richard Morse]

You probably want to do this:

INSERT INTO T (v) VALUES ('The'), ('walrus'), ('was'), ('Paul')

The above is a multiline insert which will work fine, and if you have
defined the 'v' column to be unique, it'll be impossible to insert any
duplicate data into it.

This will depend on the database backend. Oracle 8i, at least, doesn't
seem to support multiline inserts. (If I'm wrong, _please_ someone tell
me!)

So it may be necessary to do something like:

my $sql = "insert into t (v) values (?)";
my $sth = $db->prepare($sql);
foreach (qw/the walrus was paul/) {
$sth->execute($_);
}
$sth->finish();

HTH,
Ricky

 

[ctcgag]

(e-mail address removed) (Kevin Collins) wrote in message

MySQL has the "INSERT IGNORE" syntax, I don't know if Access does or not.

You noticed that SQL isn't my strong suite. Are you saying the only
way to insert 4 rows is to execute 4 individual SQL statements?

It certainly isn't the only way. You can also use a vector of values,
if Access supports it. ...values ("The"),("walrus"),("was")....

Sounds like a lot of overhead. Is there another way?

Both Oracle and MySQL have specific bulk loading tools you can use. I
don't know if access does or not.

What if I have
not 4, but 40,000 records I want to insert?

Probably the best solution is call insert 40,000 times. It isn't like
you have to carry the bits over the data bus/ethernet by hand. If the
performance is absolutely critical, well, I probably wouldn't use Access
anyway.

If you are using RaiseError, you could wrap your insert in eval {} and
catch the unique key violation error. But with a vectorized insert, I
don't know if it would insert all values other than the errors, all values
upto the first error, or none of the values. You'd have to look in the
Access documentation or just try it and see.

Xho

 

[ctcgag]

This will depend on the database backend. Oracle 8i, at least, doesn't
seem to support multiline inserts. (If I'm wrong, _please_ someone tell
me!)

And if it did, I still wouldn't use them. It is difficult to use
those inserts with bind variables, and bind variables are the way to
go, especially with Oracle.

Xho

 

[Brian McCauley]

my $sql = "insert into t (v) values (?)";
my $sth = $db->prepare($sql);
foreach (qw/the walrus was paul/) {
$sth->execute($_);
}
$sth->finish();

Don't forget execute_array().

So far it's not widely supported by the backends so is equivalent to
the above. However if execute_array() is ever directly supported by
the DBD you are using the improvement could be significant.

--
\\ ( )
. _\\__[oo
.__/ \\ /\@
. l___\\
# ll l\\
###LL LL\\