Paul DiSciascio wrote:
> I have a CSV file contains records with a unique randomized ID as the
> primary key. I retrieve a copy of the file daily and I don't have
> control over when old records are removed from the file. I would like
> to simply import this data into a table each day, ignoring the rows
> where I've already imported them (i.e. the primary key already exists
> in the database); however, if I attempt an import on a CSV file that
> contains a row in which the key already exists in the table, I get an
> error about uniqueness and none of the rows are imported. Is there a
> way to change this behavior, simply ignoring the pieces that fail and
> continuing on?
>
You can't use the shell's .import command to do that directly. You need
to use an "insert or ignore" SQL command, but the .insert command is
implemented with a normal insert SQL command.
You will first to import your data into a temp table with the same
column layout as your real table.
create temp table t_temp as select * from t_real limit 1;
delete from t_temp;
Now you can import into the temp table without any conflicts because it
is empty.
.import ....
Finally you can insert the new records into the main table using an
insert or ignore commnad.
insert or ignore into t_real select * from t_temp;
Any existing records will be retained in t_real, new records will be
added. If you want to replace any existing records in t_real you could
use an insert or replace instead.
insert or replace into t_real select * from t_temp;
HTH
Dennis Cote
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
