MySQL Bugs: #54910: Export data to SQL Script doesn't quote values

Bug #54910	Export data to SQL Script doesn't quote values
Submitted:	30 Jun 2010 8:30	Modified:	6 Jan 2011 16:05
Reporter:	Tony Walker	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S4 (Feature request)
Version:	5.2 OSS	OS:	Windows (XP SP3)
Assigned to:	Alfredo Kojima	CPU Architecture:	Any

Description:
In the SQL Editor, I would like the ability to export a recordset (or selected records in a recordset) out to a SQL script (i.e. an INSERT or UPDATE statement) for each record selected.

Export button allows export of a recordset out to CSV/XML etc, and the right-click button allows export of the values, comma-delimited, either quoted or unquoted, but both of these options require significant editing externally to transform into good SQL (especially in the latter case if you have a mix of numerics and .

The reason for this is that it would greatly increase my productivity in the development environment (i.e. create data within a sandbox/development environment, then export SQL to create the same data in multiple production environments).

Thanks for your time.

How to repeat:
.

Suggested fix:
Suggested operation: Right-click on selected records in recordset, select "Export..." from menu, choose SQL as an option here.

Many thanks for writing a feature request.

We will discuss this.

This has been implemented recently

Just tested this in the latest version (5.2.28), with a mix of numeric and text fields. The insert statement created is invealid, as it doesn't format the data for inserting. For example:
Table:
delimiter $$

CREATE TABLE `run` (
  `ID` int(10) NOT NULL AUTO_INCREMENT,
  `Run_Name` varchar(255) DEFAULT NULL,
  `Run_Description` varchar(255) DEFAULT NULL,
  `Security_Level` int(1) DEFAULT '1',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8$$

Exported data to SQL insert and got the following:
INSERT INTO `table` (ID,Run_Name,Run_Description,Security_Level) VALUES (1,TestScreens,Non user specific screens,7);

The values are not valid SQL - the text fields should be quoted.

the generated insert statements don't include quote chars for text or date columns

Regarding the last comment and the original feature request:

"Export button allows export of a recordset out to CSV/XML etc, and the right-click button allows export of the values, comma-delimited, either quoted or unquoted, but both of these options require significant editing externally to transform into good SQL (especially in the latter case if you have a mix of numerics and text)"

My request was around these values - I assumed that, as you were extracting from the database direct and would know the datatypes of the fields, you could put the correct formatting around the field values. If this isn't possible, that's OK, but the feature of addition the 'INSERT INTO x (field1, field2) VALUES' is the least time-consuming part of converting the CSV output to valid SQL - it's the addition of quotes and good formatting of the values after the file has been produced that's very difficult/impossible to automate. 

Thanks for your efforts.

Changing the synopsys to reflect the bug

fix confirmed in repository

An entry has been added to the 5.2.31 changelog:

The generated INSERT statements did not include quote characters for TEXT or DATE columns.