Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to import data from excel file to an oracle table inserting a surrogate key

Posted on 2011-09-02
5
Medium Priority
?
690 Views
Last Modified: 2013-12-07
How can I import data from an excel file to a table whose primary key is a sequence. When inserting the values a primary key have to be inserted which is usually generated from a sequence. I cannot disable the primary constraint as there other tables that depend on it.
I can insert the primary key manually in the excel file but I would like to avoid it.
How can I insert the values from excel to an oracle table creating values for the primary key as if they were from the instruction seq.nextvalue.
0
Comment
Question by:diteps06
  • 2
  • 2
5 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36476401
Can you save the file as a CSV and use sql loader?

I believe I have seen some examples using sequences with sql loader. On mobile and cannot verify it.
0
 
LVL 21

Accepted Solution

by:
oleggold earned 668 total points
ID: 36482334
I would recommend instead sqlloader use external tables , available from 9i it can really speed up the solution ,especially if it's recurring project .Then If You want to load a simple surrogate key, You can easily do it with Oracle sequence .You will need to use .new value of the sequence first time and .current value for each key You inserting
0
 
LVL 21

Expert Comment

by:oleggold
ID: 36482357
Other option is to automate the whole solution and build excel connection to Oracle ,just add automatic sequence column to excel worksheet and create Your insert macros /queries inside excel itself .
See my answer to this question from 2005:
http://www.experts-exchange.com/Database/Oracle/Q_20837792.html
0
 
LVL 8

Assisted Solution

by:Ghunaima
Ghunaima earned 668 total points
ID: 36482648
You can import the datra from excell to a new table which dont have any constraints & then use that table to populate you actual table by writing a stored procedure or a sequel.
0
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 664 total points
ID: 36488441
>>I would recommend instead sqlloader use external tables ,...

If the file is on the database server this is an option.  Not sure about 'speeding' up the process.

Sql Loader can be executed from any client machine.

>>build excel connection to Oracle ,just add automatic sequence column to excel worksheet

The danger here is the possibility of getting the generated number from Excel out of sync with the Oracle sequence generator.

You would need to:
1: Ensure no new rows were inserted during this process.
2: Get the 'next' sequence value to start your Excel generator
3: insert the rows
4: reset the Oracle sequence to the appropriate 'Next' number.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

963 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question