Home » SQL & PL/SQL » SQL & PL/SQL » query about INSERT
query about INSERT [message #190094] Tue, 29 August 2006 04:49 Go to next message
Raoul
Messages: 20
Registered: April 2005
Junior Member
Hi,

Could someone please help with the following querry:

I have 2 schemas SCH1 and SCH2 which have exactly the same tables name and structure. I want to update SCH2.tab1 table with some records from SCH1.tab1 table. WIth my limites sql, I am tempted to use the querry:

INSERT INTO SCH2.tab2 values(select * from SCH1.tab1 where column1 like '%some_value%') -> this does not work.

The selected records do not exist in SCH2.tab2
There are about 200 columns in tab1.

I have also created an export dump file from SCH1 using query="""select * from SCH1.tab1 where column1 like '%some_value%'""" but the imp to SCH2 did not work.

Thanks,
Raoul
Re: query about INSERT [message #190108 is a reply to message #190094] Tue, 29 August 2006 05:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
INSERT INTO SCH2.tab2 (select * from SCH1.tab1 where column1 like '%some_value%')


What exactly did you do when you 'Created an Export Dump File using this query'?
Re: query about INSERT [message #190114 is a reply to message #190094] Tue, 29 August 2006 05:25 Go to previous messageGo to next message
Raoul
Messages: 20
Registered: April 2005
Junior Member
I used the following:

exp sch1/password file='pathhere' tables=tab1 query="""select * from tab1 where column1 like '%somevalue%%'"""

This successfully created a dmp file but the import:

imp system/password file='pathhere' fromuser=SCH1 touser=SCH2

did not work. I am not sure if I can import a dump that only contains a set of rows also I anot sure wether imp works this way. I used imp before but I always delete all the data from a table first.

Thanks
Raoul
Re: query about INSERT [message #190116 is a reply to message #190114] Tue, 29 August 2006 05:28 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What errors did the IMP give you?

Did you try the modified query I suggested. If that failed, post the error message.
Re: query about INSERT [message #190120 is a reply to message #190108] Tue, 29 August 2006 05:38 Go to previous message
Raoul
Messages: 20
Registered: April 2005
Junior Member
Sorry I did not realise the query was different from mine.

After running the query I got the following error message:

ORA-00997: illegal use of LONG datatype

(The * marker is underneath the * in the select statement)

Previous Topic: Inserting a PL/SQL block
Next Topic: interview question
Goto Forum:
  


Current Time: Sat Dec 03 22:02:18 CST 2016

Total time taken to generate the page: 0.20633 seconds