Inserting values in one field from numerous fields in SQL

Question

I have large table, with around 200 fields. Around a 100 of those fields are to be mapped to one field when creating a dimension table from it. The problem is I have to add the values of those 100 fields one-by-one through like a 100 insert statements. Is there like a loop or something with which i can achieve this more efficiently?

Here's an example of the code:

    insert into DimTableA(visit_no, patient, facility, icd9, icd9_flag, ip_op)
    select D.registration, D3.med_number, D3.Hosp_Id, D.final_diagnosis_18,'d',IF(D3.Admit_Type in(1,2,3),'Inpatient','Outpatient'),
    from svch_dischs3_s D3, svch_diags_s D

    insert into DimTableA(visit_no, patient, facility, icd9, icd9_flag, ip_op)
    select D.registration, D3.med_number, D3.Hosp_Id, D.final_diagnosis_19,'d',IF(D3.Admit_Type in(1,2,3),'Inpatient','Outpatient'),
    from svch_dischs3_s D3, svch_diags_s D

    insert into DimTableA(visit_no, patient, facility, icd9, icd9_flag, ip_op)
    select D.registration, D3.med_number, D3.Hosp_Id, D.final_diagnosis_20,'d',IF(D3.Admit_Type in(1,2,3),'Inpatient','Outpatient'),
    from svch_dischs3_s D3, svch_diags_s D;

......... and so on

The only field name that changes is the 'icd9' input( i.e D.final_diagnosis_18, final_diagnosis_19, final_diagnosis_20 ....)

Any help would be really appreciated, lads. :)

Answer 1

You could unpivot those columns using a cross join to a fixed set of numbers like this:

insert into DimTableA (
  visit_no,
  patient,
  facility,
  icd9,
  icd9_flag,
  ip_op
)
select
  D.registration,
  D3.med_number,
  D3.Hosp_Id,
  case N.number
    when 1 then D.final_diagnosis_18
    when 2 then D.final_diagnosis_19
    when 3 then D.final_diagnosis_20
    ...
  end,
  'd',
  IF(D3.Admit_Type in(1,2,3),'Inpatient','Outpatient')
from
  svch_dischs3_s D3,
  svch_diags_s D,
  (
    select 1 as number union all
    select 2           union all
    select 3           union all
    ...  /* up to the number of columns to unpivot */
  ) N

You could create and populate a persistent numbers table instead of the inline view and use the necessary subset of that table in your query. In that case the query would change like this

...
from
  svch_dischs3_s D3,
  svch_diags_s D,
  numbers N
where N.number between 1 and … /* the number of columns to unpivot */

Answer 2

You could try running a single INSERT with a UNION in the sub select.

Your current SQL has a Cartesian join in it so I am going to take a guess that you are missing a join somewhere. Anyway, this should give you an idea of what I am driving at:

insert into DimTableA(visit_no, patient, facility, icd9, icd9_flag, ip_op)
select D.registration, D3.med_number, D3.Hosp_Id, D.final_diagnosis_18,'d',IF(D3.Admit_Type in(1,2,3),'Inpatient','Outpatient')
from svch_dischs3_s D3, svch_diags_s D
-- where D3.???? = D.????
union
select D.registration, D3.med_number, D3.Hosp_Id, D.final_diagnosis_19,'d',IF(D3.Admit_Type in(1,2,3),'Inpatient','Outpatient')
from svch_dischs3_s D3, svch_diags_s D
-- where D3.???? = D.????
union
select D.registration, D3.med_number, D3.Hosp_Id, D.final_diagnosis_20,'d',IF(D3.Admit_Type in(1,2,3),'Inpatient','Outpatient')
from svch_dischs3_s D3, svch_diags_s D
-- where D3.???? = D.????;

Here is a more theoretical example:

create table dimTable
(id int unsigned not null default 0,
field varchar(50)
);

create table sourceTable
(id int unsigned not null default 0,
field1 varchar(50),
field2 varchar(50),
field3 varchar(50),
field4 varchar(50),
field5 varchar(50)
);

insert into sourceTable (id,field1,field2,field3,field4,field5) values (1,"hello","these","are","some","values");
insert into sourceTable (id,field1,field2,field3,field4,field5) values (2,"hello2","these2","are2","some2","values2");
insert into sourceTable (id,field1,field2,field3,field4,field5) values (3,"hello3","these3","are3","some3","values3");
insert into sourceTable (id,field1,field2,field3,field4,field5) values (4,"hello4","these4","are4","some4","values4");
insert into sourceTable (id,field1,field2,field3,field4,field5) values (5,"hello5","these5","are5","some5","values5");

insert into dimTable (id, field)
select id,field1 from sourceTable
union
select id,field2 from sourceTable
union
select id,field3 from sourceTable
union
select id,field4 from sourceTable
union
select id,field5 from sourceTable;

select *
from dimTable;

Hope it helps!

Answer 3

A stored procedure would solve your problem. You may only require to pass field values to be inserted and the suffix to the D.final_diagnosis_ field.

An example code snippet is shown below:

drop procedure if exists proc_insert_icd9;
delimiter //
create procedure proc_insert_icd9( final_diagnosis_suffix int )
    begin
        declare suffixId int;
        declare sql_insert_str varchar( 255 );
        declare sql_select_str varchar( 255 );
        declare sql_temp varchar( 255 );
        set suffixId = final_diagnosis_suffix;

        set sql_insert_str = 'insert into DimTableA(visit_no, patient, facility, icd9, icd9_flag, ip_op) ';
        set sql_select_str = concat( 'select D.registration, D3.med_number, D3.Hosp_Id, D.final_diagnosis_', suffixId, ', ''d'', IF(D3.Admit_Type in(1,2,3), ''Inpatient'', ''Outpatient'' ) from svch_dischs3_s D3, svch_diags_s D' );

        select concat( sql_insert_str, sql_select_str ) into @sql_temp;

        prepare stmt from @sql_temp;
        execute stmt;
    end;
//
delimiter ;

Now try calling the procedure that many number of times you require with correct suffix value for final_diagnosis field as parameter.
Example:

call proc_insert_icd9( 18 );
call proc_insert_icd9( 19 );

Note: You can modify procedure to include

1. more input parameters to handle a where clause, etc..
2. a where clause to the select statement.