RE: Insert/Update from file
How much data? I have a process which does this against a billion row
table.
If you don't have a lot of data:
You can use Load (I don't recommend it). Turn violations on for the table,
set your unique indexes to filter. Load the data. Dupes will be inserted
into the table and noted in the violation tables.
You can use dbload (better). When issuing the command, allow for a lot of
errors. Duplicates will be rejected and noted in the log file - although it
won't be pretty.
You can use HPL (same sort of thing). Except that duplicates will be nicely
listed in a reject file that looks just like your input file in format.
If you have a lot of data (what I do):
1 - load the new data into a temp table (I use raw). Update stats.
(optimizer doesn't know how many rows are in an external table)
2 - hash join the keys from the new data and the old data into a second temp
table. (relax I'll write it in SQL in a minute)
3 - If the new table has any rows in it, then you have dupes. Otherwise you
may safely append the temp table.
4 - (Assuming you got dupes). Append the keys from the first temp table
(your new data) to the second temp table.
5 - Select key(s) from second temp table having count(*)=1 into a third temp
table. These are your non-duplicated rows.
6 - hash join the third temp table with your original new data and insert
into destination table.
This sort of process turned our original insert job (using unique indices)
from a 9 hour job to a 20 minute job under XPS.
Again in SQL (using the antique syntax):
-- 1)
create temp table new_data
(key integer,
data char(20));
load from "input_file" insert into new_data;
update statistics for table new_data;
-- 2)
select a.key
from new_data a, table_to_be_updated b
where a.key=b.key
into temp dup_check with no log;
-- 3) are there rows in dup_check? If not then:
insert into table_to_be_updated select * from new_data;
-- 4) there are dupes:
insert into dup_check select * from new_data;
update statistics for table dup_check;
-- 5) get the unique keys:
select key from dup_check group by 1 having count(*)=1 into temp good_data
with nolog;
-- 6)
insert into table_to_be_updated select a.
from new_data a, good_data b
where a.key=b.key;
cheers
j.
> -----Original Message-----
> From: aztecs@my-deja.com [mailto:aztecs@my-deja.com]
> Sent: Wednesday, February 07, 2001 7:37 AM
> To: informix-list@iiug.org
> Subject: Insert/Update from file
>
>
> Could anyone tell me if the following is possible in INFORMIX:
>
> 1. Use UNLOAD to create an input file (fields separated by "|")
> 2. Try to insert these records into a table (same specs as the one in
> step two but in a different database).
> 3. If the insert fails because it is a duplicate perform an update
> instead
>
> Can this be done in a command file? What would I use LOAD, dbload???
>
> Thanks for any help!
>
>
> Sent via Deja.com
> http://www.deja.com/
>
Unless I missed something, the original poster was
trying to do what's been termed an "UPSERT" operation.
I.e. insert if it's not already there, update if it is.
Jack, I don't see where your process performs the
updater part. You seem to be just inserting then
non-dups. Again, unless I read it wrong; if so,
please clear me up on that.
The most recent versions of XPS support something
like UPSERT via their "delete join" mechanism.
It is a two step process, something like this:
1. delete from target
using target, new
where target.key = new.key
2. insert into target
select * from new
-cs
Sent via Deja.com
http://www.deja.com/