I need to fetch data from database for backup in the form of insert statements
I need to do it on a button click in c#. So i think an sql query or stored procedure will be appropriate to do this, rather than mysqldump.
Secondly I need them for all tables. Instead of writing table and column names. They should be fetched from information_schema, because the query will not need to be changed for different scema
If there already exists a solution, please guide me.
Update : I have prepared a solution, it is posted, still looking for the better one.
I have made a complex but acceptable solution. But needs improvement.
This is a complex procedure with complex coding especially the query which fetches all rows of all columns into a single result by
group_concat and formats with a complex concatenation.
Need it simplified, efficient and working in all scenarios.
Some details of my solution : Following is the important part, other is just conditions/Looping (I am not handy with documentation also it needs time and suggestions, someone might help me in its formatting and improvement, Sorry for any inconvenience, however I will be glad for any help from you and me)
Note: group_concat(yourColumn separator ' --anySeparator-- ') is merging all rows of your column as one such that Rows are separated by --anySeparator--
select group_concat(column_name separator '`,`') into @cns1 from information_schema.columns where table_schema=dn and table_name=@tn;
1 : column_names are got as a single value separated by
`,` => @cs1 = id`,`ename`,`did select group_concat(column_name separator '`,"\',\'",`') into @cns2 from information_schema.columns where table_schema=dn and table_name=@tn;
2 : column_names are got as a single value separated by
`','` => @cn2 = id`','`ename`','`did set @cns1=concat("`",@cns1,"`"); set @cns2=concat("`",@cns2,"`");
3: Missing letter (`) is put at beginning and end of Column names
set @res=concat(@res," insert into ",@tn,"(",@cns1,") values ('");
4: Simply makes
res= " insert into emp(`id`
) values(" Here you can see why have I put separators (MySql Formatting is achieved)
set @temp := ''; set @q := concat("select group_concat(concat(",@cns2,") separator \"'),('\") from ",dn,".",@tn, " into @temp");
Above is the most crucial statement It gets all data rows from table as rows of a single column and further these rows are merged being separated by
concat(",@cns2,") gets values of all columns in a single one.
5.2 After outer most concat now @q is
@q = "select group_concat(`id`','`ename`','`,did` separator '),(' from mydb.emp into @temp";
5.3 : group_concat will merge all rows of that combined column into one value.
Columns values will be joined through separators existing in @cns2 and rows level joining will be with
prepare s1 from @q; execute s1;deallocate prepare s1; set @res = concat(@res,@temp,");");
@q is executed
set @res = concat(@res,@temp,");");
6 : And We will get result as
res was = insert into emp(`id`,`ename`,`did`) values (' @temp = 1','e1','4'),('2','e2','4'), ('3','e3','2'),('4','e4','4'),('5','e5','3
@res = concat(@res,@temp,");"); we get
insert into emp(`id`,`ename`,`did`) values ('1','e1','4'),('2','e2','4'), ('3','e3','2'),('4','e4','4'),('5','e5','3);