Creating Sequence Value After Data Import [message #495943] |
Wed, 23 February 2011 07:35 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/4940aa9c00206d88eae385f743399d1b?s=64&d=mm&r=g) |
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 #495950 is a reply to message #495949] |
Wed, 23 February 2011 08:06 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Roachcoach
Messages: 1576 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 #495961 is a reply to message #495959] |
Wed, 23 February 2011 08:52 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Roachcoach
Messages: 1576 Registered: May 2010 Location: UK
|
Senior Member |
|
|
Indeed but then the act of "faking" a PK becomes insane ![Smile](images/smiley_icons/icon_smile.gif)
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 #495964 is a reply to message #495963] |
Wed, 23 February 2011 09:01 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Roachcoach
Messages: 1576 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](images/smiley_icons/icon_smile.gif)
@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 #495972 is a reply to message #495967] |
Wed, 23 February 2011 09:55 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
oraQ wrote on Wed, 23 February 2011 16:26but 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/4940aa9c00206d88eae385f743399d1b?s=64&d=mm&r=g) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ThomasG
Messages: 3212 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 Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 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>
|
|
|