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
developer-***@public.gmane.org
--
Follow us on Twitter: http://twitter.com/barefeetware/
Like us on Facebook: http://www.facebook.com/BareFeetWare
Post by KrzysztofOk 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
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.