Hi!

There is a bug in the automatic creation of insert statements. After selecting a result set of a query, I want to create the corresponding INSERT-statements using the export function of the SQL Editor. I would expect something like 'INSERT INTO table1 (col2,col4) VALUES ('X','Y');' for a SELECT-statement like 'SELECT col2, col4 FROM table1', but I get 'INSERT INTO table1 VALUES ('X','Y');'. This generated statement will fail, because the table 'table1' has more than 2 columns.
Field names can only be left out, when all columns are filled in the correct sequence, otherwise Oracle wouldn't know, which fields to fill.

Best regards,

Peter Wagner.

Hi,

How do you generate that INSERT command (place and leading actions)?
Does your table have blob or object type columns?

Thanks

: Hi!

: There is a bug in the automatic creation of insert statements. After
: selecting a result set of a query, I want to create the corresponding
: INSERT-statements using the export function of the SQL Editor. I would
: expect something like 'INSERT INTO table1 (col2,col4) VALUES ('X','Y');'
: for a SELECT-statement like 'SELECT col2, col4 FROM table1', but I get
: 'INSERT INTO table1 VALUES ('X','Y');'. This generated statement will
: fail, because the table 'table1' has more than 2 columns.
: Field names can only be left out, when all columns are filled in the correct
: sequence, otherwise Oracle wouldn't know, which fields to fill.

: Best regards,

: Peter Wagner.

Hi,

in the result window of the query I select the "Export" option of the context menu (clicking the right mouse button in the window), where I choose "SQL Syntax" for the filetype.

Best regards,

Peter.

: Hi,

: How do you generate that INSERT command (place and leading actions)?
: Does your table have blob or object type columns?

: Thanks

This is not actually the SQL Syntax for the table from where you SELECT, this is the syntax for the result set.

If you do a

SELECT tab1.col1, tab2.col2 FROM tab1, tab2 WHERE tab1.colx = yab2.colz

and then you choose Export menu and choose other_table.sql as the output file name then generated SQL will look like the following

INSERT INTO other_table VALUES (val1, val2);

As you see the syntax is based on the output, not on the source

If you need SQL INSERTS generated for a specific table with all table columns included you can use several methods to get that done. For example, you can open that table in the Table Painter tool, right-click on the table box and select Export from the popup menu or use Export in the main File menu.

: Hi,

: in the result window of the query I select the "Export" option of
: the context menu (clicking the right mouse button in the window), where I
: choose "SQL Syntax" for the filetype.

: Best regards,

: Peter.

Thank you.

Now I see what you did there, you generate a script which creates a new table AND the INSERT statements for all selected columns.

Using the table painter tool, I can only generate plain text files, where the data is seperated by blanks.

: This is not actually the SQL Syntax for the table from where you SELECT, this
: is the syntax for the result set.

: If you do a

: SELECT tab1.col1, tab2.col2 FROM tab1, tab2 WHERE tab1.colx = yab2.colz

: and then you choose Export menu and choose other_table.sql as the output file
: name then generated SQL will look like the following

: INSERT INTO other_table VALUES (val1, val2);

: As you see the syntax is based on the output, not on the source

: If you need SQL INSERTS generated for a specific table with all table columns
: included you can use several methods to get that done. For example, you
: can open that table in the Table Painter tool, right-click on the table
: box and select Export from the popup menu or use Export in the main File
: menu.

If that table is no very big you can try View/Edit table data first and then choose Export from the menu.

: Thank you.

: Now I see what you did there, you generate a script which creates a new table
: AND the INSERT statements for all selected columns.

: Using the table painter tool, I can only generate plain text files, where the
: data is seperated by blanks.