Solved

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

Posted on 2011-09-02
5
624 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 76

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 167 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 167 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 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 166 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to recover a database from a user managed backup

895 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now