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

x
?
Solved

How do I maintain an auto increment identity column with direct path loads?

Posted on 2008-10-08
4
Medium Priority
?
394 Views
Last Modified: 2013-12-18
Hi Experts,
I have a very large flat text file.  I will be loading this file into an oracle database using direct path loading with committs happening every 1,000,000 rows.  I would like to maintain an autoincrementing identity column for each row that is imported.  This is easy to do with conventional path loading with setting up a sequence and an trigger on inserts.  How can this be done with direct path loads?
0
Comment
Question by:GIFT22075
  • 2
4 Comments
 
LVL 15

Accepted Solution

by:
Shaju Kumbalath earned 2000 total points
ID: 22668945
eg:

LOAD DATA
INFILE 'xyz.dat'
BADFILE 'xyz.bad'
LOG xyz.log
INSERT INTO TABLE empmast
(emp_no POSITION(1:6) INTEGER,
emp_name POSITION(7:31) CHAR,
seq_no SEQUENCE(MAX,1))

0
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 22668953
where seq_no is auto incremented
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 22669319
or use RECNUM if you simply need a 1..N count

LOAD DATA
INFILE 'xyz.dat'
BADFILE 'xyz.bad'
LOG xyz.log
INSERT INTO TABLE empmast
(emp_no POSITION(1:6) INTEGER,
emp_name POSITION(7:31) CHAR,
seq_no recnum)



0
 

Author Comment

by:GIFT22075
ID: 22669368
thanks you guys i'm testing out the solutions now and will post feedback
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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

927 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