Thread: Intentionally inserting duplicates without aborting

I'm fairly new to SQL, so please bear with me.

I'm writing an application in which I represent an item in a table,
and I would also like a history table in which I represent the item as
it exists on various dates.

The item's primary key is an integer.  I have represented the history
table with a compound key of the item's primary key and a column
indicating the state of that item.  Eg.

create table item (item_id        integer        primary key,state        varchar(8)
);

create table item_hist (item_id        integer        primary key,state        varchar(8),date        date,
primary key (item_id, state)
);

It happens that I have to query another application for the status of
these items, and it is vastly more efficient to get the status of all
the items than it is to query for each individually.

What I would like to do (and have manage to kludge together in Perl
DBI) is to attempt to insert (item_id, state, today's date) into
item_hist, and in the cases where that item/state combo exists
already, the insert will fail.  However, if I am using {AutoCommit =>
0}, the whole transaction bombs, so I can only get away with this if
I'm AutoCommit'ing.

Any thoughts on how I can do this with AutoCommit => 0?  I would
rather not have to look up all the status rows to figure out if an
error will occur.

--Dave

At 18:59 +0300 on 12/08/1999, disser@sdd.hp.com wrote:


> What I would like to do (and have manage to kludge together in Perl
> DBI) is to attempt to insert (item_id, state, today's date) into
> item_hist, and in the cases where that item/state combo exists
> already, the insert will fail.  However, if I am using {AutoCommit =>
> 0}, the whole transaction bombs, so I can only get away with this if
> I'm AutoCommit'ing.
>
> Any thoughts on how I can do this with AutoCommit => 0?  I would
> rather not have to look up all the status rows to figure out if an
> error will occur.

Are you saying that inserting a (some_item_id, some_state, some_date) and
waiting for an error is more efficient than doing a SELECT 1 FROM ... WHERE
item_id = some_item_id, state = some_state and seeing whether or not you
got any rows? It shouldn't be much of a difference, since both operations
simply go through the index.

In any case, if what you want to do is to insert a bulk of data, and only
have the non-duplicates be inserted, then perhaps you should create a temp
table of ited id and state, insert all the data into it (using COPY for
faster insertions) and then:

INSERT INTO item_history
SELECT item_id, state, now() FROM temp_table t
WHERE NOT EXISTS ( SELECT * FROM item_history i WHERE  i.item_id = t.item_id AND i.state = t.state
);

Or maybe you meant that the insertion is done directly from the table item?
Then you don't need a temporary table. The general looks are the same,
though you would probably want to add things to the WHERE clause.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma