 |
|
|
|
Next: Wrong Results from Select on sysindexes Subquery
|
| Author |
Message |
External
Since: Mar 04, 2005
Posts: 5
|
(Msg. 1) Posted: Wed Dec 14, 2005 12:54 pm
Post subject: Copy a record from 1-table to another
Archived from groups: comp>databases>oracle>misc (more info?)
|
|
|
Greetings...
This is an extremely simplified version of the actual problem, just to
make it easier.
I have 2-tables: R(a) and S(a,b)
I want to do the following (which doesn't work), and I don't know what
I need to do to
make it work. This is on a Oracle 9i system.
cursor c1 is select * from r;
r_rec c1%ROWTYPE;
begin
open c1;
loop
fetch c1 into r_rec;
exit when c1%NOTFOUND;
-- The next statement does not work since #columns in r_rec doesn't
match S.
-- This is the problem...what do I do to make this work?
insert into s values r_rec;
end loop;
end;
I tried something like: insert into s values(r_rec,1) as well, but that
is syntactically incorrect.
Anyone have any suggestions?
Thanks,
Joe |
|
| Back to top |
|
 | |
External
Since: Mar 04, 2005
Posts: 5
|
(Msg. 2) Posted: Wed Dec 14, 2005 2:43 pm
Post subject: Re: Copy a record from 1-table to another [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Unfortunately, while this is A solution, I'm trying to avoid it.
We are talking about well over 200-attributes in the table in the real
system.
All I want to do is copy R (which is over 200 attributes) to S which is
the same structure with 3-additional attributes, and populate the
3-attributes when I do this.
I tried types, records, varrays, etc.
Joe
Sybrand Bakker wrote:
> On 14 Dec 2005 12:54:33 -0800, "JoeT" wrote:
>
> >Anyone have any suggestions?
>
>
> insert into s(col1, col2, col3, col4 ....)
> select col1, col2, col3, col4
> from r
>
> will work like a charm, and moreover: it will be a lot faster compared
> to your nonscalable solution.
>
> --
> Sybrand Bakker, Senior Oracle DBA |
|
| Back to top |
|
| |
External
Since: Dec 09, 2004
Posts: 44
|
(Msg. 3) Posted: Wed Dec 14, 2005 4:00 pm
Post subject: Re: Copy a record from 1-table to another [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
You don't need to bother with PL/SQL. Just create a table with what
you want in it with the CTAS syntax, and then insert into the final
table. Don't use select *, of course, specify the columns. I'm sure
there are more trick ways to do this, but think on it:
SQL> desc t0
Name Null? Type
----------------------------------------- --------
----------------------------
C VARCHAR2(30)
SQL> desc t1
Name Null? Type
----------------------------------------- --------
----------------------------
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(1 
CREATED NOT NULL DATE
LAST_DDL_TIME NOT NULL DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL> create table jjj as select * from t0,t1;
Table created.
SQL> desc jjj
Name Null? Type
----------------------------------------- --------
----------------------------
C VARCHAR2(30)
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(1
CREATED NOT NULL DATE
LAST_DDL_TIME NOT NULL DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL>
jg
--
@home.com is bogus.
Hate to admit it, but I'm with Bush on this one:
http://www.signonsandiego.com/uniontrib/20051214/news_1n14congress.html >> Stay informed about: Copy a record from 1-table to another |
|
| Back to top |
|
| |
External
Since: Feb 16, 2005
Posts: 199
|
(Msg. 4) Posted: Wed Dec 14, 2005 5:55 pm
Post subject: Re: Copy a record from 1-table to another [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
On 14 Dec 2005 12:54:33 -0800, "JoeT" wrote:
>Anyone have any suggestions?
insert into s(col1, col2, col3, col4 ....)
select col1, col2, col3, col4
from r
will work like a charm, and moreover: it will be a lot faster compared
to your nonscalable solution.
--
Sybrand Bakker, Senior Oracle DBA >> Stay informed about: Copy a record from 1-table to another |
|
| Back to top |
|
| |
External
Since: Mar 04, 2005
Posts: 5
|
(Msg. 5) Posted: Wed Dec 14, 2005 6:59 pm
Post subject: Re: Copy a record from 1-table to another [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
TOAD won't work because this will have to run in the middle of the
night from a shell script.
I can't see how the first part of this response is will solve the
problem.
I have data in one table that all I want to do is move to another table
and add 3-additional fields.
Perhaps bulk collect/insert will work, but I have been unable to make
it work given the documentation and examples I have found so far.
Thanks anyway.
Joe
Sybrand Bakker wrote:
> On 14 Dec 2005 14:43:47 -0800, "JoeT" wrote:
>
> >All I want to do is copy R (which is over 200 attributes) to S which is
> >the same structure with 3-additional attributes, and populate the
> >3-attributes when I do this.
>
> And this requires only a little cutting and pasting...
>
> just
> spool x
> desc r
> spool off
> edit x and transform it into s
> OR
> Use Toad, select all columns, right click and generate an INSERT
> statement.
> Your solution is a *disaster* in terms of performance as you are
> fetching record for record. You would at least need to do a BULK
> COLLECT and BULK INSERT,
> and coding that is WAY MORE WORK than
> INSERT SELECT
>
> Geeez, don't people learn to WORK anymore nowadays?
> It sure looks like!!
>
> --
> Sybrand Bakker, Senior Oracle DBA >> Stay informed about: Copy a record from 1-table to another |
|
| Back to top |
|
| |
External
Since: Dec 16, 2003
Posts: 217
|
(Msg. 6) Posted: Wed Dec 14, 2005 7:52 pm
Post subject: Re: Copy a record from 1-table to another [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
"JoeT" wrote in message
> TOAD won't work because this will have to run in the middle of the
> night from a shell script.
> I can't see how the first part of this response is will solve the
> problem.
>
> I have data in one table that all I want to do is move to another table
> and add 3-additional fields.
> Perhaps bulk collect/insert will work, but I have been unable to make
> it work given the documentation and examples I have found so far.
>
> Thanks anyway.
>
> Joe
>
>
> Sybrand Bakker wrote:
> > On 14 Dec 2005 14:43:47 -0800, "JoeT" wrote:
> >
> > >All I want to do is copy R (which is over 200 attributes) to S which is
> > >the same structure with 3-additional attributes, and populate the
> > >3-attributes when I do this.
> >
> > And this requires only a little cutting and pasting...
> >
> > just
> > spool x
> > desc r
> > spool off
> > edit x and transform it into s
> > OR
> > Use Toad, select all columns, right click and generate an INSERT
> > statement.
> > Your solution is a *disaster* in terms of performance as you are
> > fetching record for record. You would at least need to do a BULK
> > COLLECT and BULK INSERT,
> > and coding that is WAY MORE WORK than
> > INSERT SELECT
> >
> > Geeez, don't people learn to WORK anymore nowadays?
> > It sure looks like!!
> >
> > --
> > Sybrand Bakker, Senior Oracle DBA
>
It is an easy script. There is no need to use pl/sql; it will be a lot
slower than just issuing the sql statement. It is also pretty easy to put
it in a dbms_job and have it run whenever you want.
Jim >> Stay informed about: Copy a record from 1-table to another |
|
| Back to top |
|
| |
External
Since: Feb 16, 2005
Posts: 199
|
(Msg. 7) Posted: Wed Dec 14, 2005 7:55 pm
Post subject: Re: Copy a record from 1-table to another [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
On 14 Dec 2005 14:43:47 -0800, "JoeT" wrote:
>All I want to do is copy R (which is over 200 attributes) to S which is
>the same structure with 3-additional attributes, and populate the
>3-attributes when I do this.
And this requires only a little cutting and pasting...
just
spool x
desc r
spool off
edit x and transform it into s
OR
Use Toad, select all columns, right click and generate an INSERT
statement.
Your solution is a *disaster* in terms of performance as you are
fetching record for record. You would at least need to do a BULK
COLLECT and BULK INSERT,
and coding that is WAY MORE WORK than
INSERT SELECT
Geeez, don't people learn to WORK anymore nowadays?
It sure looks like!!
--
Sybrand Bakker, Senior Oracle DBA >> Stay informed about: Copy a record from 1-table to another |
|
| Back to top |
|
| |
External
Since: Jun 30, 2005
Posts: 24
|
(Msg. 8) Posted: Wed Dec 14, 2005 7:55 pm
Post subject: Re: Copy a record from 1-table to another [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
JoeT wrote on 14.12.2005 23:43:
> Unfortunately, while this is A solution, I'm trying to avoid it.
> We are talking about well over 200-attributes in the table in the real
> system.
> All I want to do is copy R (which is over 200 attributes) to S which is
> the same structure with 3-additional attributes, and populate the
> 3-attributes when I do this.
>
I tend to believe that writing the posts for this group took you more
time then it would have taken you to write down the select with the 200
columns 
Thomas >> Stay informed about: Copy a record from 1-table to another |
|
| Back to top |
|
| |
External
Since: Mar 04, 2005
Posts: 5
|
(Msg. 9) Posted: Thu Dec 15, 2005 5:34 am
Post subject: Re: Copy a record from 1-table to another [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Care to share how easy the script is?
Nobody has come up with a reasonable solution so far.
Joe
Jim Kennedy wrote:
> "JoeT" wrote in message
>
> > TOAD won't work because this will have to run in the middle of the
> > night from a shell script.
> > I can't see how the first part of this response is will solve the
> > problem.
> >
> > I have data in one table that all I want to do is move to another table
> > and add 3-additional fields.
> > Perhaps bulk collect/insert will work, but I have been unable to make
> > it work given the documentation and examples I have found so far.
> >
> > Thanks anyway.
> >
> > Joe
> >
> >
> > Sybrand Bakker wrote:
> > > On 14 Dec 2005 14:43:47 -0800, "JoeT" wrote:
> > >
> > > >All I want to do is copy R (which is over 200 attributes) to S which is
> > > >the same structure with 3-additional attributes, and populate the
> > > >3-attributes when I do this.
> > >
> > > And this requires only a little cutting and pasting...
> > >
> > > just
> > > spool x
> > > desc r
> > > spool off
> > > edit x and transform it into s
> > > OR
> > > Use Toad, select all columns, right click and generate an INSERT
> > > statement.
> > > Your solution is a *disaster* in terms of performance as you are
> > > fetching record for record. You would at least need to do a BULK
> > > COLLECT and BULK INSERT,
> > > and coding that is WAY MORE WORK than
> > > INSERT SELECT
> > >
> > > Geeez, don't people learn to WORK anymore nowadays?
> > > It sure looks like!!
> > >
> > > --
> > > Sybrand Bakker, Senior Oracle DBA
> >
>
> It is an easy script. There is no need to use pl/sql; it will be a lot
> slower than just issuing the sql statement. It is also pretty easy to put
> it in a dbms_job and have it run whenever you want.
> Jim >> Stay informed about: Copy a record from 1-table to another |
|
| Back to top |
|
| |
| Related Topics: | copy table to another schema programatically - anyone have code snippets that copy a table from one schema to another? i usually use toad to do this but i really would like to find more automated way.
How to copy a tablespace? - Hi! I want to move a tablespace from one instance to another, they have different names. Ex: user test_user with tablespace test_user_data, want to move this to user prod_user with tablespace prod_user_data. Is it possible with exp - imp ? Have in min...
copy/paste keys - I would like your help on the following situation: Anybody who has worked with oracle forms 6i on solaris on sparc machines with sun keyboard, should know the usefulness of the "dedicated" keys copy, paste (and cut) that the sun keyboards have....
how to copy value of an in out parameter from calling proc.. - I am calling a procedure(order) from a calling procedure (caller). Order uses an In out parament (Ser_no) Order is supposed to return a serial number. how can i copy the value returned by Order to a local variable of Caller. thanks.
Find the first record, for every day - Hi ! I have a tricky problem, and i can't solve it. I have the following table T1, with the following fields and records: Date_ Status ---------- ------- 2005-12-01 2 2005-12-01 1 2005-12-17 1 2005-12-18 2 2005-12-18 10 I want to generate... |
|
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum
|
|
|
|
|
|
|
FAQ
Search
Profile
Private Messages
Log in
