inserting records from one user to another user!Urgent!

[Guest_imported]

HI all,
Let say I have two users under one schema. Scott1 and Scott2 with different passwords. Now I need to insert some records into scott2 from scott1. What is an easy way of doing this?

 

[Turkbear]

You seem to be confused..The 2 users cannot be 'under the same schema' - Scott1 and Scott2 are separate schemas
( perhaps you meant in the same instance ) :

So,[ very rough sample code ]
login as Scott2 then
insert into my_table select something from Scott1.his_table
(this assumes lots of stuff - the existance of a table called my_table in Scott2 schema with same structure as Scott1.his_table;
That Scott2 has select privs on Scott1.his_table, etc...)

 

[mkey]

I tried to run this code(oracle tells me that 7 records selected). It seem like its working but when I go into the scott2(table_a) i don't see any data.

insert into scott1.table_a(PIPEID,DWGNUM,DWGNUM2,ZOOMXP,PREVNUM,PREVDESC,ORGPAGE,DWGSCALE,STASTART,STAEND,STARTEQ,
,ENDEQ,ABSTATION1,ABSTATION2,TOWNSHIP,COUNTY,STATE,TOWNUM,RANGENUM,SECTIONNUM,SHEETDIR,DWGSTATUS,STATUS
,USERID,EFFECTIVE_DATE,EDIT_TRACKID,SHAREID,CROSSOVER,LINKID,RELOCATION,EDITDWG,MPSTART,MPEND,UTMZONE) SELECT PIPEID,DWGNUM,DWGNUM2,ZOOMXP,PREVNUM,PREVDESC,ORGPAGE,DWGSCALE,STASTART,STAEND,STARTEQ,
ENDEQ,ABSTATION1,ABSTATION2,TOWNSHIP,COUNTY,STATE,TOWNUM,RANGENUM,SECTIONNUM,SHEETDIR,DWGSTATUS,STATUS,
USERID,EFFECTIVE_DATE,EDIT_TRACKID,SHAREID,CROSSOVER,LINKID,RELOCATION,EDITDWG,MPSTART,MPEND,UTMZONE
FROM scott2.table_a WHERE pipeid= 14004;

Can you please tell me what's going on?

 

[Turkbear]

You inserted into scott1.table_a so why would
you expect to see it in scott2.table_a?
( or was that a typo ?)

Try the select statement alone to see what is returned from scott2.table_a ( where pipeid = 14004 );

A note:
If the structure of the 2 tables is identical ( same number
and type and order of columns) and you want the entire record, you can leave out the (,,,,,,,) stuff and just use

insert into scott1.table_a select * from scott2.table_a;