I imported data into a database table using import table data, how can i save the current setting because i need to do that process many time later.
It's very easy to do this with Automation Designer. I made a video.
First I set up the import in Automation Designer, then re-open the data import window to run it again, then just right-click the action to run it again.
it worked, thank you
Is there any way to save this configuration somewhere? if i need to work in other machine?
Right-Click on App1, choose Export/Import
worked. Thank you
Hi John,
Hope all is well.
facing issue in import data table, Merge Option.
Error executing query for row # 2:
ORA-00928: missing SELECT keyword
Continue?
Parameter as per ur instaraction
Can u Help on Above.
More Thanks in Advance
Hi Ahmed,
I don't have enough information to tell you what is wrong.
Which version of Toad is this?
Can you turn on spool sql and try it again? Then post the spool SQL output so I can see the whole SQL that is causing the problem. Main menu -> database -> Spool SQL.
Also some screen shots of how you configured the import.
Thanks
Toad Ver 13.1.0.78
Spool SQL Result
-- Session: SYS@ORCL_LOCAL
-- Timestamp: 16:37:13.026
select x.*, x.rowid from EMIS2000.INVENTORIES x;
Hi Ahmed.
Are you checking ALL of the table columns as key fields? I think that would have caused this problem in Toad version 13.1 It was fixed in version 13.3. If possible, check fewer columns to be used as the key fields.
If that's not it, I still need more info.
Turn on spool sql
Open Import Table Data.
Go through all the steps
Post screen shots of each step here
Post the entire spool sql output here (it will be hundreds of lines long)
-- Session: SYS@ORCL_LOCAL
-- Timestamp: 17:16:23.729
select x.*, x.rowid from EMIS2000.INVENTORIES x;
I just updated you from "new user" to "member" so you can post more than one screen shot.
I can see in your screen shot that spool SQL output has over 100 lines. Please post the whole thing, not just the last SQL.
Can you post the "Preview file and define fields" step?
See how mine has field names in bold? (where I underlined in red) If the correct field names are not selected there, click the dropdown arrow (where I circled in red) to select the correct database field for each file in the spreadsheet.
Also please post this step:
If every column is checked in "Key Field", that can cause a problem.
I really want to see the primary key fields and spool SQL. I don't see any problem in what you've posted so far.
Please edit your last post, remove a few attachments and add those.
Dear John,
IF WE SELECT INSERT OPTION , THERE IS NO ISSUE IN INVENTORIES TABLE.
EASILY INSERT NEW RECORDS.
BUT IN UPDATE OR MERGE HAVING ISSUE OF ROWID?
The Key fields are required for update and merge. That's why I keep asking for screen shots of the step where key is specified. What do you have in this step?
Also, can you please post your entire spool SQL output. Maybe then I can determine exactly what is going wrong.
see key fields.
spoolout put
-- Session: SYS@ORCL_LOCAL
-- Timestamp: 21:09:13.137
select * from EMIS2000.INVENTORIES where 0=1;
-- Session: SYS@ORCL_LOCAL
-- Timestamp: 21:09:13.217
select column_Name
from all_tab_columns
where owner = :own
and table_name = :tn
and default_length is not null
and default_length > 0;
:own(VARCHAR[8])='EMIS2000'
:tn(VARCHAR[11])='INVENTORIES'
-- Session: SYS@ORCL_LOCAL
-- Timestamp: 21:09:13.228
insert into EMIS2000.INVENTORIES
()
values
();
Error: ORA-00928: missing SELECT keyword
-- Session: SYS@ORCL_LOCAL
-- Timestamp: 21:09:15.365
Commit:
-- Session: SYS@ORCL_LOCAL
-- Timestamp: 21:09:15.619
select x.*, x.rowid from EMIS2000.INVENTORIES x;
Thanks. I can tell by the Spool SQL that the import wizard can't find the mappings from the excel file to your database table, but I don't know what that's happening. I just tried this with the same version of Toad, while logged in as SYS, importing into a non-SYS table, and it worked OK for me. But maybe something about your table or data is causing the problem.
Can you send me a "Create table" statement so I can build your table and try it myself?
Also, if you can send me your Excel file? It's ok if you take out all of the data except one or two rows. And you can of course change any data that has sensitive information.
Please send it to me at john.dorlon@quest.com
Thanks.