How to get SQL statement of table data-CodePudding

I have an SQLite database. I have a table of data. I want to get all the data in the table as a SQL INSERT INTO statement.

For instance, consider we have the following data in the table:

id	name
1	Lion
2	Tiger

And, I want the SQL statement of them as follows:

INSERT INTO table_name (id, name)
VALUES (1, "Lion"),
(2, "Tiger");

Is it possible to get like this from the table using SQL?

CodePudding user response：

You can look for dump options if you are using some sort of GUI tool. Else, you can use mysqldump.

Command reference from this question:

mysqldump --complete-insert --lock-all-tables --no-create-db 
--no-create-info --extended-insert --password=XXX -u XXX 
--dump-date yyy > yyy_dataOnly.sql

Assuming this is for MySQL/MariaDB based DB.

CodePudding user response：

If you want to insert all the rows of the table table_name1 to the table table_name2 you can use SELECT instead of VALUES:

INSERT INTO table_name2 (id, name)
SELECT id, name
FROM table_name1;

If you want a query that returns the INSERT statement you can use GROUP_CONCAT():

SELECT 'INSERT INTO table_name(id, name) VALUES ' || 
        GROUP_CONCAT('(' || QUOTE(id) || ',' || QUOTE(name) || ')') || ';' sql
FROM table_name;

See the demo.