Home » SQL & PL/SQL » Client Tools » Creating Sequence Value After Data Import (Windows XP, Oracle 11g)
Creating Sequence Value After Data Import [message #495943] Wed, 23 February 2011 07:35 Go to next message
oraQ
Messages: 57
Registered: January 2011
Member
I have an excel sheet as follows: I have to import the data in the excel sheet to oracle database table through TOAD.

name1 name2 name3
ABS SDFG FHTR
DFR GHJK HJK
... ... ...

Now, I need an id column along with these three that would have id numbers like that of a sequence. That means, suppose I have 1000 records in the excel sheet, then the table should have as many numbers automatically after data import. Can anyone guide in this regard?

Thanks in advance.
Re: Creating Sequence Value After Data Import [message #495946 is a reply to message #495943] Wed, 23 February 2011 07:47 Go to previous messageGo to next message
Roachcoach
Messages: 1513
Registered: May 2010
Location: UK
Senior Member
alter table foo add PK number;
update table foo set PK = rownum;


Damned sure wouldn't recommend this however, it sounds as though your very model is flawed. I'd look long and hard at that before pulling the code above.
Re: Creating Sequence Value After Data Import [message #495949 is a reply to message #495946] Wed, 23 February 2011 07:56 Go to previous messageGo to next message
oraQ
Messages: 57
Registered: January 2011
Member
Roachcoach,will you be clear what you said?

[Updated on: Wed, 23 February 2011 07:57]

Report message to a moderator

Re: Creating Sequence Value After Data Import [message #495950 is a reply to message #495949] Wed, 23 February 2011 08:06 Go to previous messageGo to next message
Roachcoach
Messages: 1513
Registered: May 2010
Location: UK
Senior Member
I mean what I posted will work but its use points towards broken data and relational models.

I may be mistaken but pulling data into oracle from an excel source then effectively shoehorning a primary key (which will relate to nothing else in the database) in just yells bad planning to me.

Basically it looks like you're trying to work around a process flawed at the outset. Whilst you can do it, it would probably be prudent to sort out the model used instead. Doing stuff like this tends to just store problems up for later. It's like turning up a car radio so you don't hear a knocking noise any more.


In short - be sure doing this is the best course of action before doing it.
Re: Creating Sequence Value After Data Import [message #495954 is a reply to message #495950] Wed, 23 February 2011 08:34 Go to previous messageGo to next message
oraQ
Messages: 57
Registered: January 2011
Member
I agree with you Roachcoach, but then I need to generate auto numbers for my table. I cannot insert for thousands manually. I am using an utility called 'import table data' in TOAD. However, I am unable to proceed further for doing so for my table. Can you throw some more light in this attempt.
I appreciate for valuable comments.
Re: Creating Sequence Value After Data Import [message #495956 is a reply to message #495954] Wed, 23 February 2011 08:38 Go to previous messageGo to next message
cookiemonster
Messages: 13014
Registered: September 2008
Location: Rainy Manchester
Senior Member
Are you going to do this task multiple times?
Does the table have pre-existing data?
Re: Creating Sequence Value After Data Import [message #495957 is a reply to message #495956] Wed, 23 February 2011 08:40 Go to previous messageGo to next message
Roachcoach
Messages: 1513
Registered: May 2010
Location: UK
Senior Member
I gave you an example of what to do in my first post - I just then went on to say I think its a crap idea, even if it does work (which it does).
Re: Creating Sequence Value After Data Import [message #495959 is a reply to message #495957] Wed, 23 February 2011 08:47 Go to previous messageGo to next message
cookiemonster
Messages: 13014
Registered: September 2008
Location: Rainy Manchester
Senior Member
Not if there's pre-existing data with an fk pointing to it.
Re: Creating Sequence Value After Data Import [message #495961 is a reply to message #495959] Wed, 23 February 2011 08:52 Go to previous messageGo to next message
Roachcoach
Messages: 1513
Registered: May 2010
Location: UK
Senior Member
Indeed but then the act of "faking" a PK becomes insane Smile

See earlier comments about this being a Really Bad Idea™

imo, if this is the case - the key should be generated by the original source in accordance with business rules and carried by the import, not shoehorning it in after the data is imported. Doubly so if this is an append operation.

Like I said - you can, but it screams bad planning to me.

[Updated on: Wed, 23 February 2011 08:55]

Report message to a moderator

Re: Creating Sequence Value After Data Import [message #495963 is a reply to message #495961] Wed, 23 February 2011 08:57 Go to previous messageGo to next message
cookiemonster
Messages: 13014
Registered: September 2008
Location: Rainy Manchester
Senior Member
Depends - the original source might really be an excel spreadsheet. In which case you probably want to be using sqlloader and a sequence.
Re: Creating Sequence Value After Data Import [message #495964 is a reply to message #495963] Wed, 23 February 2011 09:01 Go to previous messageGo to next message
Roachcoach
Messages: 1513
Registered: May 2010
Location: UK
Senior Member
I wouldn't, unless I had no other choice. It implies there's no unique identifier out there "in the business", makes interaction with the DB team(s) much harder if there are queries, implies possible duplication across rows which would cause issues later on.

I go back to my standpoint: You can, but I think its a really bad idea which will store up problems for later days.

However this is devolving (if it hasn't already) into good/bad practice debate rather than the solution Smile



@OP - You can't use what I posted for a table with existing data, only holds for a new table.

[Updated on: Wed, 23 February 2011 09:03]

Report message to a moderator

Re: Creating Sequence Value After Data Import [message #495967 is a reply to message #495964] Wed, 23 February 2011 09:26 Go to previous messageGo to next message
oraQ
Messages: 57
Registered: January 2011
Member
No I don't have any pre-existing data. I am importing fresh data from an excel sheet , but in the mean time there must be ids that are to be auto-generated to avoid the problem of manual insert into the table.
Re: Creating Sequence Value After Data Import [message #495969 is a reply to message #495956] Wed, 23 February 2011 09:38 Go to previous messageGo to next message
cookiemonster
Messages: 13014
Registered: September 2008
Location: Rainy Manchester
Senior Member
cookiemonster wrote on Wed, 23 February 2011 14:38
Are you going to do this task multiple times?

Re: Creating Sequence Value After Data Import [message #495972 is a reply to message #495967] Wed, 23 February 2011 09:55 Go to previous messageGo to next message
ThomasG
Messages: 3205
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
oraQ wrote on Wed, 23 February 2011 16:26
but in the mean time there must be ids that are to be auto-generated to avoid the problem of manual insert into the table.


How do you think IDs will prevent the manual insertion into the table? The same work-around you come up with in regards to the excel data someone else will come up with when he wants to insert data "manually".
Re: Creating Sequence Value After Data Import [message #496085 is a reply to message #495972] Thu, 24 February 2011 06:21 Go to previous messageGo to next message
oraQ
Messages: 57
Registered: January 2011
Member
No, I mean to say that inserting id numbers for thousands of records is surely going to be difficult. Though, it is a bad practice to create sequence value after data import, but then how to put numbers sequentially for that col for one-time data import? Also, is there any way to do auto numbers if I will go for inserts multiple times?
Thanks.

[Updated on: Thu, 24 February 2011 06:47]

Report message to a moderator

Re: Creating Sequence Value After Data Import [message #496091 is a reply to message #496085] Thu, 24 February 2011 06:58 Go to previous messageGo to next message
cookiemonster
Messages: 13014
Registered: September 2008
Location: Rainy Manchester
Senior Member
Create a sequence, then either:
a) create a before insert row trigger on the table to assign the sequence to the correct column.
b) skip using TOAD and use sqlloader instead, that'll allow you reference the sequence directly (don't know the syntax off the top of my head but you can look it up easily enough.
Re: Creating Sequence Value After Data Import [message #496168 is a reply to message #496091] Fri, 25 February 2011 00:42 Go to previous messageGo to next message
knw15pwr
Messages: 134
Registered: March 2010
Senior Member
You know it would be much easier for you to add your sequence (or id as you say) in the excel spreadsheet. Then import this column as the PK. It took me just about 3 seconds to add the id to the entire sheet about 65k records. It will save you time and effort for coding the logic later. (Besides it seems like you are doing a 1 time load)
Re: Creating Sequence Value After Data Import [message #496281 is a reply to message #496168] Fri, 25 February 2011 14:30 Go to previous messageGo to next message
ThomasG
Messages: 3205
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
knw15pwr's solution is also the (in general) better approach logically.

I you do any transfers of any data, and you have want to have some sort of "ID" associated with each row of the data, then ADD THAT ID IN THE SOURCE SYSTEM. (screaming intentionally here), otherwise that ID will be pretty worthless in the long run.

Re: Creating Sequence Value After Data Import [message #496332 is a reply to message #496281] Sat, 26 February 2011 15:29 Go to previous message
Barbara Boehmer
Messages: 8804
Registered: November 2002
Location: California, USA
Senior Member
The question of whether there will be future loads or inserts or associated data in other tables remains unanswered. If the table is to have an auto-incrementing primary key, then I would do as Cookie Monster suggested. I would create an id column, make that id column the primary key, create a sequence, and create a before insert row trigger to automatically populate that primary key id column with the next value of the sequence. That should take care of current and future loads and inserts, wherever the data comes from, through whatever method. You should be able to use Toad or SQL*Loader or whatever you like to do the inserts. I have provided an example using SQL*Loader below.

-- test.dat (file containing data):
name1 name2 name3
ABS SDFG FHTR
DFR GHJK HJK


-- test.ctl (SQL*Loader control file):
options (skip=1)
load data
infile test.dat
into table target_table
fields terminated by whitespace
(name1, name2, name3)


-- table to load data into:
SCOTT@orcl_11gR2> create table target_table
  2    (id     number primary key,
  3  	name1  varchar2 (5),
  4  	name2  varchar2 (5),
  5  	name3  varchar2 (5))
  6  /

Table created.


-- sequence:
SCOTT@orcl_11gR2> create sequence test_seq
  2  /

Sequence created.


-- trigger that automatically populates id with sequence:
SCOTT@orcl_11gR2> create or replace trigger test_trigger
  2    before insert on target_table
  3    for each row
  4  begin
  5    :new.id := test_seq.nextval;
  6  end test_trigger;
  7  /

Trigger created.

SCOTT@orcl_11gR2> show errors
No errors.


-- load data using SQL*Loader:
SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log


-- results:
SCOTT@orcl_11gR2> select * from target_table
  2  /

        ID NAME1 NAME2 NAME3
---------- ----- ----- -----
         1 ABS   SDFG  FHTR
         2 DFR   GHJK  HJK

2 rows selected.

SCOTT@orcl_11gR2> 

Previous Topic: SQL Developer - Disable "save password" functionality
Next Topic: Closed connection error after procedure debug in SQL Develoiper
Goto Forum:
  


Current Time: Thu Feb 22 14:42:10 CST 2018

Total time taken to generate the page: 0.02793 seconds