Solved

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

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to recover a database from a user managed backup
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

747 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

10 Experts available now in Live!

Get 1:1 Help Now