Multiple rows in one insert for export schema

I would like to be able to generate insert statements with multiple rows when I export a schema in SQL-format. When there is one insert statement per row, it takes very long time to transfere data from one database to another. There should also be an option to specify the max length (in bytes) of a statement. See for instance how it's done in phpMyAdmin. Regards Frode

This just seems like feature bloat to me. There are other ways to do this, e.g. consider exporting table rows as csv and using your database's bulk import facility. If on the same machine you can even do it by something like "insert into new_table select * from old_table;" after copying the schema during the create. Or even use phpmyadmin for this task.

Hi Frode, Thanks for the suggestion. As far as I know, MySQL is the only database that supports insert of multiple rows with one INSERT statement. I'll add this to our feature request list. Best Regards, Hans

Thanks The reason for the request is that I often need to copy a whole database (mostly Oracle) to a MySQL database. This is quite easy to do by doing a SQL-export, and then do some regexp to change the SQL to something MySQL can accept. I would of course love a possibility to choose a SQL dialect in the SQL export (MySql, Oracle etc.), but I don't know how difficult that would be to implement. The option to choose multiple rows per insert could then show just for the MySql dialect. It's true that I could use the CSV export instead, but that would mean more work for me to migrate the databases. Regards Frode

Other databases can be tricked into it with: INSERT INTO mytable (field_one,field_two,field_three,field_four) SELECT 'eeny','meeny','miny','mo' UNION ALL SELECT 'Tinky Winky','Dipsy','Laa-Laa','Po' UNION ALL SELECT 'Athos','Portos','Aramis','d'Artagnan' In SQL Server it's not just syntactic sugar - this approach can show marked performance improvements over single inserts for large datasets.