Home » SQL & PL/SQL » SQL & PL/SQL » insert using a view
insert using a view [message #36485] Thu, 29 November 2001 09:08 Go to next message
Tony
Messages: 190
Registered: June 2001
Senior Member
Hi, I created a view:

create or replace view update_text as
select long_datatype from table1 a where exists (select null from table2 b where a.x=b.x);

I'd like to execute this view so it inserts the results from the view into another table that also has the long_datatype. I'm not sure how to utilize the view in this sense, here's an initial attempt with copy:

copy to db1/db1@TONY -
insert table2 (long_dataype) -
using text_update;
I get an invalid sql statement with the above. Any help would be appreciated. Thanks.

----------------------------------------------------------------------
Re: insert [message #36501 is a reply to message #36485] Fri, 30 November 2001 06:22 Go to previous message
SAlapati
Messages: 12
Registered: November 2001
Junior Member
Use the Sql stmt itself instead of the view

copy to db1/db1@TONY -
insert table2 (long_dataype) -
select long_datatype from table1 a where exists (select null from table2 b where a.x=b.x);

You may also want to set the following

set arraysize N -- amount of rows the copy command will copy with each fetch
set long N -- size of your longest long
set copycommit M -- number of fetches to do before commit (N*M rows!!)

----------------------------------------------------------------------
Previous Topic: DBMS_SQL.PARSE for Oracle 7.3.4
Next Topic: Fetching Errors in a Table
Goto Forum:
  


Current Time: Thu Mar 28 04:04:03 CDT 2024