This kind of question often results in answers along the lines of application logic such as: "If the row exists get the ID else create it and then get the ID". But the SQL way of doing things is subtly different: "insert a new row where it doesn't already exist. Get the ID of the row".
Based on your description, it seems you have a schema something like this: create table Countries ( ID integer primary key not null , Name text collate nocase ) ; create table Customers ( ID integer primary key not null , Name text collate nocase , Surname text collate nocase , ID_Country integer references Countries (ID) on delete restrict on update cascade ) ; And it seems you have a long list of CSV data that could be imported like this: insert into Countries (Name) select @Country where @Country not in (select Name from Countries) ; insert into Customers (Name, Surname, ID_Country) select @Name, @Surname, (select ID from Countries where Name = @Country) ; Possibly a faster way to do it is to create a temporary table, import all your raw flat data into it, then run a single transaction to import it all into your normalised tables. If you imported it into a table called "Import", eg: create temp table Import (Name, Surname, Country) then your transaction to insert it into your normalised tables would be: begin immediate ; insert into Countries (Name) select Country from Import where Country not in (select Name from Countries) ; insert into Customers (Name, Surname, ID_Country) select Name, Surname, (select ID from Countries where Name = Country) from Import ; commit ; Tom Tom Brodhurst-Hill BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases develo...@barefeetware.com -- Follow us on Twitter: http://twitter.com/barefeetware/ Like us on Facebook: http://www.facebook.com/BareFeetWare On 04/01/2013, at 11:08 PM, Krzysztof <dib...@wp.pl> wrote: > Ok I have done this with two commnads where first check if record exist. I > tried do this with one command because I want to speed up importing from > csv. CSV has one table for example: name | surname | country. I want split > repeated columns to dictionary tables like: > > table customers > - name > - surname > - id_country // FK to table countries > > I'm looking for one command which before inserting to "customers" will look > to table countries and check if country exists, if not then insert and > return id, else return existing id. > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
