CODEWITHSUNDEEP
mysql
Dumitru Gutu
Dumitru Gutu

Reputation: 579

mysql dynamic insert based on source data

I have the following table, table name source_data

column1    values     table  col        group_flag   index
--         --         --     --         --           --    
id         1          users  oid        1            1
fName      dan        users  firstName  0            1
lName      opera      users  lastName   0            1
oid        2          users  oid        1            2
fName      fan        users  firstName  0            2
lName      popa       users  lastName   0            2
id         3          users  oid        1            3
fName      oana       users  firstName  0            3 
lName      jon        users  lastName   0            3

That data represent the mapping of data to be inserted in table mentioned in column "table". The "group_flag" represent by which column data should be grouped.

So according to these data I need have in "users" table 3 records, like

insert into users (oid,firstName,lastName) values (1,'dan','opera');
insert into users (oid,firstName,lastName) values (2,'fan','popa');
insert into users (oid,firstName,lastName) values (3,'oana','jon');

How to write the query from "source_data" table to generate such insert statements ?

Upvotes: 0

Views: 44

Answers (1)

Nightw0rk
Nightw0rk

Reputation: 419

On future, append DDL for easy explain. Check my solution

DDL

CREATE TABLE tmp (
  column1 VARCHAR(11),
  `values` VARCHAR(11),
  `table` VARCHAR(11),
  col VARCHAR(11),
  group_flag INT,
  `index` INT
);
INSERT INTO tmp(column1,`values`,`table`,col,group_flag,`index`) VALUES('id','1','users','oid',1,1);
INSERT INTO tmp(column1,`values`,`table`,col,group_flag,`index`) VALUES('fName','dan','users','firstName',0,1);
INSERT INTO tmp(column1,`values`,`table`,col,group_flag,`index`) VALUES('lName','opera','users','lastName',0,1);
INSERT INTO tmp(column1,`values`,`table`,col,group_flag,`index`) VALUES('oid','2','users','oid',1,2);
INSERT INTO tmp(column1,`values`,`table`,col,group_flag,`index`) VALUES('fName','fan','users','firstName',0,2);
INSERT INTO tmp(column1,`values`,`table`,col,group_flag,`index`) VALUES('lName','popa','users','lastName',0,2);
INSERT INTO tmp(column1,`values`,`table`,col,group_flag,`index`) VALUES('id','3','users','oid',1,3);
INSERT INTO tmp(column1,`values`,`table`,col,group_flag,`index`) VALUES('fName','oana','users','firstName',0,3); 
INSERT INTO tmp(column1,`values`,`table`,col,group_flag,`index`) VALUES('lName','jon','users','lastName',0,3);

query

select concat("insert into ",`table`,"(",GROUP_CONCAT(col),') VALUES (',GROUP_CONCAT(`values`),');') from tmp group by `index`,`table`

And result

insert into users(oid,firstName,lastName) VALUES (1,dan,opera);
insert into users(oid,firstName,lastName) VALUES (2,fan,popa);
insert into users(oid,firstName,lastName) VALUES (3,oana,jon);

is this you wanted?

Upvotes: 2

Related Questions