Table of Contents
Data file
| Data |
|
| File | 2Load_innms,innm_dosages,brands |
| Pages |
|
Upload steps & logic
Step 0- Prepare & clean original data
...
Create temp table
Code Block language sql collapse true create table _temp_innms ( id uuid not null constraint _temp_innms_pkey primary key, sctid varchar(255), name varchar(255) not null, name_original varchar(255) not null, is_active boolean default false not null, inserted_by uuid not null, updated_by uuid not null, inserted_at timestamp not null, updated_at timestamp not null )
- Source page "innm"
- Prepare data (column `name` & `name_original`)
Prepare SQL-Insert statement on page (based on template)
Example from page with formula (without CR\TAB\etc !!!):
No Format ="insert into _temp_innms (id,sctid,name,name_original,is_active,inserted_by,inserted_at,updated_by,updated_at) values (uuid_generate_v4(), '','"&A1&"', '"&D1&"', TRUE ,'4261eacf-8008-4e62-899f-de1e2f7065f0', now(),'4261eacf-8008-4e62-899f-de1e2f7065f0',now());"
Where:
'4261eacf-8008-4e62-899f-de1e2f7065f0' - system user id
A1 - name innm
D1 - name original of innm- Insert rows from calculating SQL-insert statements
Step 3 - Load forms
- Source page: "Forms"
- Create pivot table, insert `key` for dictionary `MEDICATION_FORM` prepare SQL-Insert statement on page (based on template)
Example from page with formula (without CR\TAB\etc !!!):
No Format ="INSERT into _temp_form (name, name_key,dosage_num_unit,dosage_denum_unit, container_num_unit,container_denum_unit) values('"&C1&"','"&D1&"','"&E1&"','"&F1&"','"&G1&"','"&H1&"')" - Insert rows from calculating SQL-insert statements
...
- Create temp table for medications (with mandatory column `ext_id` !!!)
Run script load medications = brands
Run script load medications = innm_dosage
- Create temp table for ingredients
Run script create ingredients for medications = brands
- Run script create ingredients for medications = innm_dosages
- Run script create medical_programs
- Run script create program_medications
- Move data from `_temp_tables` to `_real_tables` (OR Generate `SQL Inserts` from `*._temp_tables`, then rename tables name from `_temp` → `origin_name`, run SQL Inserts scripts)