How to create sql script that generates insert statements with tables data in mysql/mssql

Question

Could any one provide an example sql script that generates

Could anyone help me out. Is there a way to create a script that will generate insert statements for the table with already existing data?

There is a table with such a structure -> Country [id (int pk), name(nvarchar)] How will the script for generating such and output look like?

Output:

INSERT INTO country (NAME) VALUES ('canada');

INSERT INTO country (NAME) VALUES ('england');

INSERT INTO country (NAME) VALUES ('italy');

INSERT INTO country (NAME) VALUES ('spain');

You can easily program this in a loop using most programming languages. — juergen d
– juergen d, Commented Jul 29, 2013 at 14:25
i know I could easily write this in c#, however I need this to be done using sql — CoffeeCode
– CoffeeCode, Commented Jul 29, 2013 at 14:26
@FloDoe could you be more specific because i'm new to mysql... — CoffeeCode
– CoffeeCode, Commented Jul 29, 2013 at 14:30

Károly Nagy · Accepted Answer · 2013-07-29 14:34:54Z

1

This will do what you wanted:

select concat ("insert into `Country` (`country`) values (\"", country, "\");") from Country order by id;

Sample: http://sqlfiddle.com/#!2/efb4c/2

However you need to tune it for your schema of course.

answered Jul 29, 2013 at 14:34

Károly Nagy

1,75410 silver badges13 bronze badges

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

Comments

Darshan Mehta · Accepted Answer · 2013-07-29 14:32:01Z

0

If you want to insert the existing data again into your table (as you have mentioned in the question) then, you can write an sql like:

INSERT INTO country (NAME) SELECT NAME FROM COUNTRY;

answered Jul 29, 2013 at 14:32

Darshan Mehta

31k14 gold badges75 silver badges111 bronze badges

Comments

Flo Doe · Accepted Answer · 2013-07-29 14:36:18Z

0

You can use mysqldump to archive the thing you asked, try the following:

mysqldump -u username -ppassword --skip-triggers --compact --no-create-info --extended-insert=FALSE databasename tablename1 tablename2 ...

If you have huge tables, keep in mind that single inserts will be slower than extended ones.

Also see the docs on mysqldump: mysqldump — A Database Backup Program

answered Jul 29, 2013 at 14:36

Flo Doe

5,3912 gold badges21 silver badges18 bronze badges

Collectives™ on Stack Overflow

How to create sql script that generates insert statements with tables data in mysql/mssql

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related