#1011 "copy as insert values statements" disabled too often

Gerd Wagner

The possibility to extract data with a query and have squirrel create insert-scripts is extremely usefull for setting up tests with realistic data (or complexely interrelated data) from production. However, the function is very often disabled (unavailable from the popup menu in the result set), for instance if one of the columns contains data that is transformed with a built-in function. This is not very helpful, quite the contrary actually.

In oracle, for instance, clob column values s can be inserted from varchar values, but they must be extracted using a built-in function: "select *" will only get null-values from clob-columns even if they contain data. Selecting all columns by name and using the function "dbms_lob.substr( CLOB_COLUMN_NAME, 4000, 1) as CLOB_COLUMN_NAME" for the clob column will retrieve values from the table (the clob values being converted to varchars), but disable the "copy as insert statements" menu item. However selecting all columns by name will enable the menu-item, but will retrive only null-values from the clob columns (resulting in "insert ... ,null,...").

In general: it is a mistake to be "intelligent" and "helpfull" and disable the menu item when the result set is using transformations. It is often the case that the schemas used in test are simplified relative to the ones used in production. Also, one might want to test changes to the persistence format. There is no need to compare the values and types to the table that they came from, because they are not necessarily intended to be inserted in an identical table anyway. This is partly taken care of already, by inserting the test "PressCtrlH" instead of the table name in the generated insert-statements (which is a good idea btw).

The principle behind "PressCtrlH", that "it is not squirrel's business to where you want to insert this as long as it is not the original table", should be followed further: The "generate insert statements" should be available for any result-set at all times. It is not up to squirrel to decide which result sets that are suitable for insertion in a table somewhere.

So, finally: a request to remove complex features and code from squirrel, rather than to add it :-)


  • I've just reviewed this issue. It would be helpful to have precise steps to reproduce, and ideally some test data! This will help someone walking into this issue blind, to see what the creator sees.

  • Gerd Wagner
    Gerd Wagner

    • assigned_to: Gerd Wagner
    • milestone: --> 2.1final
  • ArneD

    The issue is still present in 3.5.0.

    If I create a data script of the whole table, everthing is fine. But if I only want to copy some selected records from a table with the "Copy as SQL INSERT-VALUES statement" the table name is missing / replaced by PressCtrlH