| Creating Sequence Value After Data Import [message #495943] |
Wed, 23 February 2011 07:35  |
 |
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  |
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 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
Indeed but then the act of "faking" a PK becomes insane 
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 |
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
@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 |
ThomasG
Messages: 3211
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 |
|
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 |
cookiemonster
Messages: 13904
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 |
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 |
ThomasG
Messages: 3211
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 |
 |
Barbara Boehmer
Messages: 9058
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>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
