Home » SQL & PL/SQL » SQL & PL/SQL » Import records from Access or Excel
Import records from Access or Excel [message #199500] Tue, 24 October 2006 16:08 Go to next message
NauticusLX
Messages: 2
Registered: October 2006
Junior Member
I just took an Intro to Oracle course, which taught that records are imput with a VALUES statement, one record at a time. This seems slow. Is there a way to import records from an existing Access table or Excel spreadsheet?

Thanks in advance.
Re: Import records from Access or Excel [message #199501 is a reply to message #199500] Tue, 24 October 2006 16:29 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oh well, you've heard only the Introduction to Oracle. What did you expect, to learn ALL about it in a few hours?

Of course there is another way to insert data into an Oracle database; you could use SQL*Loader or external tables feature to load CSV files, and - as you are interested in Access - you could read more about Oracle Heterogeneous Services.

Search for all those terms on the Internet (at the moment, I'm too lazy to do it for you).
Re: Import records from Access or Excel [message #199550 is a reply to message #199501] Wed, 25 October 2006 01:41 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I would recomment external tables or SQL*Loader, as Littlefoot suggested. HS (Heterogenous Services) are dependent on ODBC, and that is not the fasted solution around. Also, check out the Oracle Online Documentation. You will find that you don't need a "VALUES" clause to perform an insert Wink.

MHE

[Updated on: Wed, 25 October 2006 01:42]

Report message to a moderator

Re: Import records from Access or Excel [message #199565 is a reply to message #199501] Wed, 25 October 2006 02:07 Go to previous messageGo to next message
NauticusLX
Messages: 2
Registered: October 2006
Junior Member
No, I certainly didn't expect to learn anything in a few hours. This was an online course lasting 6 weeks, and the "intermediate" course was another 6 weeks. I had Oracle 9i installed on my home PC to do the excercises. There was absolutely no instruction in the use of the Enterprise Manager or the SQL Plus Worksheet, which I discovered on my own. The course was based on using the SQL Plus line editor. Now, I thought "line editing" was obsolete 30 years ago! And inputing records with the VALUES clause? Heck, even I know that's probably not done in practice anywhere, and I'm a total newbie when it comes to Oracle. That's academia for you! Anyway, thanks for the advice, I'll look into the strategy you suggest.
Re: Import records from Access or Excel [message #199579 is a reply to message #199565] Wed, 25 October 2006 03:06 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
SQL*plus is a great tool and I still use it frequently. But for the average developer it is a bit clumsy. So, on most projects other tools are used. Check out this OraFAQ page and Oracle's own SQL Developer. The most popular tools are: TOAD, SQL Navigator (both from Quest software) and PL/SQL Developer (from Allround Automations).

As for the inserts, most of the end users don't interact with tables directly but have a front end that takes care of the SQL. Are inserts done frequently with a VALUES clause? If you look at the sample scripts I posted on the board, you'll see that I tend to do that, yes. But I that's partly because my text editor allows me to type in column mode so I have to type the INSERT only once Very Happy.

MHE
Re: Import records from Access or Excel [message #199676 is a reply to message #199565] Wed, 25 October 2006 14:35 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I think the majority of insert-statements (note: not inserted records, but insert-statements) are done using the values clause!
How on earth would you insert a record in an OLTP otherwise? Save to file and sqlload it?
Previous Topic: CAST result of MONTHS_BETWEEN in to Integer
Next Topic: Information Hiding in pl/sql
Goto Forum:
  


Current Time: Tue Dec 06 13:55:38 CST 2016

Total time taken to generate the page: 0.06190 seconds