TLR TLR - 1 month ago 9
SQL Question

Best way to insert data that doesn't exist

I have the following the tables

Temp user table

The content comes from an API

-------------------------------------
| UserId | Location | Department |
-------------------------------------
| 1 | Loc1 | Dep1 |
| 2 | Loc2 | Dep40 |
| 3 | Loc100 | Dep30 |
-------------------------------------


The goal here is to insert these data, however they belong to 3 different tables :

User Table

---------------------------------------
| UserId | LocationID | DepartmentID |
---------------------------------------
| 1 | 1 | 1 |
| 2 | 2 | 2 |
--------------------------------------


Department

---------------------------------
| DepId | DepName |
---------------------------------
| 1 | Dep1
| 2 | Dep40
....
| 30 (new)| Dep40 --> New value
----------------------------------


Locations

---------------------------------
| LocId | LocName |
---------------------------------
| 1 | Loc1
| 2 | Loc240
....
| 10 (new)| Loc100 --> New value
----------------------------------


Based on this temp_user table, I should be able to add a new user and insert the missing department and locations to have something like this
Considering for example:


  • 30 is the id for Dep40

  • 10 is the id for Loc100



My user table should looks like this

---------------------------------------
| UserId | LocationID | DepartmentID |
---------------------------------------
| 1 | 1 | Dep1 |
| 2 | 2 | Dep40 |
| 3 | 10 | 30 |
--------------------------------------


Question

What is the best way to proceed this insertion? Can I do it with some triggers or should I create a stored procedure? What is the correct algorithm so?

Answer

Assuming the department name and the location name are unique, you can do something like this:

-- insert missing locations
insert into location (loc_name)
select location 
from temp_users
on conflict (loc_name) do nothing;

-- insert missing departments
insert into department (dep_name)
select department
from temp_users
on conflict (dep_name) do nothing;

-- insert new users
insert into users (id, location_id, department_id)
select tu.user_id, l.id, d.id
from temp_users tu
  join location l on l.loc_name = tu.location
  join department d on d.dep_name = tu.department
on conflict (id) do nothing; -- ignore existing users with the same ID

The above assumes that all id columns are defined as a primary key, that the ids for location and department are generated using a sequence (e.g. by defining them as serial) and that there are unique constraints defined on location.loc_name and department.dep_name.

Live example: http://rextester.com/SND63582

Comments