On 23/03/2011, at 9:03 PM, Shalom Elkin wrote: > I am new to SQLITE. Here's the challenge: > > sqlite> .dump > BEGIN TRANSACTION; > CREATE TABLE people( > id integer, > nm text); > INSERT INTO "people" VALUES(1,'Jack'); > INSERT INTO "people" VALUES(2,'Jill'); > CREATE TABLE activity( > aid integer, > act text, > foreign key (aid) references people(id) > ); > COMMIT; > PRAGMA foreign_keys=On; > sqlite> .import ac.in activity > Error: foreign key mismatch > > This is ac.in > > 1|eat > 2|sleep > 3|run > 1|drink > 2|dream > > id 3 doesn't exist, the insertion fails. Now, this was easy. what if > ac.inhad millions of rows? I am looking for some way to get a message > like "error > in line 3: foreign key mismatch". > preferably, the import would go on without actually inserting, but report > ALL errors in one pass. > > Any ideas?
I usually import into a separate table, then use SQL to process the data into the final destination. This way, I can use any dumb import tool (such as the .import command line tool) and take care of the smarts (including constraints, error logging etc) in SQL. How about this: pragma foreign_keys = on; create temp table "activity import" ( aid integer , act text ) ; create table "import error" (aid integer); .import ac.in "activity import" begin immediate; insert into "import error" select aid from "activity import" where aid not in (select id from "people"); insert into "activity" select * from "activity import" where aid in (select id from "people"); commit; or, you can add the required people on the fly: begin immediate; insert or ignore into "people" (id) select aid from "activity import" where aid not in (select id from "people"); insert into "activity" select * from "activity import"; commit; For this to work, you probably want to define the primary key in people: CREATE TABLE people ( id integer primary key not null , nm text ) ; Tom BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases develo...@barefeetware.com -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
