CODEWITHSUNDEEP
mysql
Scheintod
Scheintod

Reputation: 8115

mysql select query which returns sql inserts

Is it possible to select from mysql so that the result are insert statements?

I have to transfer "some rows" from my online database to my development workstation. This is always a lot of manual work.

It would be great if i could somehow:

select * from mytable where mycondition=myvalue AS SQL INSERT

and would get something like

INSERT INTO mytable VALUES ( 1, 'foo' );
INSERT INTO mytable VALUES ( 2, 'bar' );
...

This I could use to copy and paste into my local database. Is this (or something like that) possible?

EDIT: I'm looking for an easy/lazy solution which doesn't require much effort.

EDIT2: The mysqldump --where solution works for my question and 80% of my "real world" usage so thanks for the answer. Still it would be great if I could use complete queries for selection of the rows so that I could use joins or alter values.

Upvotes: 0

Views: 30

Answers (2)

Barmar
Barmar

Reputation: 781004

Use CONCAT to construct the INSERT statement:

SELECT CONCAT("INSERT INTO mytable VALUES(", id, ", '", somecolumn, "');\n") AS sql_insert
FROM mytable
WHERE mycondition = myvalue

Upvotes: 2

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181290

Yes. You need mysqldump utility which does exactly that.

Upvotes: 2

Related Questions