mysql dynamic insert based on source data

Question

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 ?

Nightw0rk · Accepted Answer · 2016-05-04 07:51:18Z

2

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?

answered May 4, 2016 at 7:51

Nightw0rk

4194 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

fancyPants Over a year ago

+1, but you should add a ORDER BY col in the GROUP_CONCAT(). Otherwise you might get weird results.

Nightw0rk Over a year ago

if only one group of several identical column, but this delirium

fancyPants Over a year ago

Dude, what? Don't you get it? You might end up with different column order or values inserting into the wrong column. Also you're missing some parantheses in there.

Collectives™ on Stack Overflow

mysql dynamic insert based on source data

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related