How to get SQL statement of table data

Question

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?

Tag your specific database - postgres/mysql/sql server/oracle/db2/sqllite ? Some tools have an option to generate data as insert statements, or you can write a query to do so, depending on your requrements. Only the single specific table? Any specified table? — Stu, Dec 26 '21 at 18:29

score 1 · Answer 1 · answered Dec 26 '21 at 18:25

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.

score -1 · Accepted Answer · answered Jan 22 '23 at 19:24

You can build it using StringBuilder:

val builder = StringBuilder()

builder.append("INSERT INTO ")
   .append(TABLE_NAME)
   .append(" (id, name) VALUES ")

entities.forEach { entity -> 
  builder.append("\n(")
     .append(entity.id)
     .append(", \"")
     .append(entity.name)
     .append("\"),")
}

How to get SQL statement of table data

2 Answers2