Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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
?
683 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 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 77

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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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.

715 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