Database Backup Sql Query

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 appropri

Solution 1:

To get Data of whole database - SqlFiddle Demo

To get data of only one table - - SqlFiddle Demo

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@cns1from
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@cns2from 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` , `ename` ,`did` ) 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 '),('

5.1 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;deallocateprepare 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

And after @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);

Solution 2:

selectconcat("insert into users (id,name,password) values ('",id,"'"), 
concat(",'",username,"'"),
concat(",'",password,"'),") INTO OUTFILE 'c:\\datas\\asd.txt' from users