MySQL query to generate INSERT statements for all tables

MySQL query to generate INSERT statements with columns for all tables in the current database:

-- Increase limit for GROUP_CONCAT() to avoid text cutoff
SET SESSION group_concat_max_len = 1048576;
SELECT 
    CONCAT(
        '\n\n', 
        GROUP_CONCAT(CONCAT(
            'INSERT INTO `', 
            table_name, 
            '` (', 
            columns, 
            ') VALUES ()'
        ) SEPARATOR '\n\n'), 
        '\n'
    ) AS result 
FROM (
    SELECT table_name, GROUP_CONCAT(CONCAT('`', column_name, '`')) AS columns
    FROM information_schema.columns 
    WHERE table_schema = DATABASE()
    GROUP BY table_name 
    ORDER BY table_name ASC
) AS tables\G

Sample output:

*************************** 1. row ***************************
result:

INSERT INTO `actor` (`id`,`organization_id`,`name`,`created_at`,`created_by`,`updated_at`,`updated_by`,`deleted_at`,`deleted_by`) VALUES ()

INSERT INTO `organization` (`id`,`name`,`created_at`,`created_by`,`updated_at`,`updated_by`,`deleted_at`,`deleted_by`) VALUES ()

1 row in set (0.00 sec)

Bonus: Get list of columns for each table in alphabetical order, useful for comparing databases on different servers 🙂

SELECT table_name, GROUP_CONCAT(column_name ORDER BY column_name ASC) AS columns
FROM information_schema.columns 
WHERE table_schema = DATABASE()
GROUP BY table_name 
ORDER BY table_name ASC\G