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

Reply via email to