SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
DB Tools - SQL Editor - Export of query result

 
Reply to topic    SoftTree Technologies Forum Index » DB Audit, DB Mail, DB Tools View previous topic
View next topic
DB Tools - SQL Editor - Export of query result
Author Message
Peter Wagner



Joined: 03 Mar 2006
Posts: 11

Post DB Tools - SQL Editor - Export of query results Reply with quote

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.

Fri Mar 03, 2006 5:05 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6505

Post Re: DB Tools - SQL Editor - Export of query result Reply with quote

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.

Fri Mar 03, 2006 9:42 am View user's profile Send private message
Peter Wagner



Joined: 03 Mar 2006
Posts: 11

Post Re: DB Tools - SQL Editor - Export of query result Reply with quote

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

Fri Mar 03, 2006 9:59 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6505

Post Re: DB Tools - SQL Editor - Export of query result Reply with quote

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.

Fri Mar 03, 2006 10:15 am View user's profile Send private message
Peter Wagner



Joined: 03 Mar 2006
Posts: 11

Post Re: DB Tools - SQL Editor - Export of query result Reply with quote

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.

Fri Mar 03, 2006 10:58 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6505

Post Re: DB Tools - SQL Editor - Export of query result Reply with quote

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.

Fri Mar 03, 2006 2:43 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » DB Audit, DB Mail, DB Tools All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.