Trevor
I agree. That's why I asked. This is Dan's question (BTW).
If it's 1:M then I see nested loops with the outer checking
for breaks in the data. If it's 1:1 then it's simple. And,
If it is 1:1, why two tables?
Chris
-----Original Message-----
From: Williams Trevor
[mailto:oracledba-ezmlmshield-x91770852.[Email address protected]
Sent: Wednesday, August 31, 2005 5:06 PM
To: LazyDBA Discussion
Subject: RE: PL/SQL Solution?
Chris
Reading between the lines, I am assuming that you cannot insert into
table1/2 from worktable, then insert into table2/1 using worktable inner
join table1/2.
I am guessing that there is a 1:M relationship otherwise you'd be using
the same surrogate key. Perhaps you can add the "M" sequence number to
your worktable first, populate table1/2 then insert into table2/1 using
worktable inner join table1/2.
You really need to provide more information such as candidate keys,
table sizes, oracle version, how you generate the sequence numbers,
relationship between table1/2 (and worktable), are you "bulk inserting",
...
Trevor
-----Original Message-----
From: Chris Hagemaier
[mailto:oracledba-ezmlmshield-x80613020.[Email address protected]
Sent: Thursday, 1 September 2005 7:19 AM
To: LazyDBA Discussion
Subject: RE: PL/SQL Solution?
Dan-
Table1, Table2. One to many? One to One?
-----Original Message-----
From: Dan Pappas
[mailto:oracledba-ezmlmshield-x6377119.[Email address protected]
Sent: Wednesday, August 31, 2005 3:06 PM
To: LazyDBA Discussion
Subject: PL/SQL Solution?
Hello All:
I have a new ETL process I need to develop. I think the way to solve
this is a PL/SQL program. I have some books I am using for reference,
but I thought I would post to the board for some help.
I have 3 tables: Table1, Table2, and WorkTable.
a. The data in WorkTable is used to populate Table1 and Table2.
b. Table1 has 5 columns(col1-col5). Column col1 is populated with a
sequence number(surrogate key).
c. Table2 has 10 columns (col1-col10). Column col1 is populated with a
squence number(surrogate key).
d. Table1.col5 is populated with the data value from Table2.col1.
e. Table2.col10 is populated with the data value from Table1.col1.
f. All other columns in Table1 and Table2 are populated with data from
the WorkTable.
I think I need to have a PL/SQL program that reads the data (cursor)
from the WorkTable and then insert a record in Table1 and save the value
of Table1.col1 to a variable to be inserted into Table2.col10 and insert
a record into Table2 and save the value of Table2.col1 to a variable to
be inserted into Table1.col5.
That is, the data in the WorkTable updates Table1 and Table2, with the
surrogate keys in Table1 and
Table2 "cross-referenced" back to each table.
Thanks in advance for any assistance and remember I am a novice at
PL/SQL programming.
Dan
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com To unsubscribe:
see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com By using this list you agree
to these terms:http://www.lazydba.com/legal.html
--
This message has been scanned for viruses and dangerous content by
MailScanner, and is believed to be clean.
Douglas County, Oregon
www.co.douglas.or.us
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com To unsubscribe:
see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com By using this list you agree
to these terms:http://www.lazydba.com/legal.html
DISCLAIMER:
Disclaimer. This e-mail is private and confidential. If you are not the
intended recipient, please advise us by return e-mail immediately, and
delete the e-mail and any attachments without using or disclosing the
contents in any way. The views expressed in this e-mail are those of the
author, and do not represent those of this company unless this is
clearly indicated. You should scan this e-mail and any attachments for
viruses. This company accepts no liability for any direct or indirect
damage or loss resulting from the use of any attachments to this e-mail.
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
--
This message has been scanned for viruses and dangerous content by
MailScanner, and is believed to be clean.
Douglas County, Oregon
www.co.douglas.or.us
Oracle LazyDBA home page