Solved

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

Posted on 2011-09-02
5
651 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 77

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 77

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SP result not being displayed 5 69
oracle forms question 9 40
How do I partition this table on date? 5 55
Automate an Oracle update in Excel 7 70
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

751 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