Home » SQL & PL/SQL » SQL & PL/SQL » Is there anything like that?
Is there anything like that? [message #244479] Wed, 13 June 2007 01:23 Go to next message
psix666
Messages: 51
Registered: April 2007
Location: Azerbaijan
Member

Hi guys.

FoxPro got one very interesting thing:

Select Table_1
Scatter MEMVAR
INSERT into Table_2 from MEMVAR


What does this do:
1. Select's all of the rows from table Table_2 to the memory like an array.
2. Insert's all columns from Table_2 into Table_1 where colum names are equals.

Is there anything like that in Oracle except Dynamic SQL.
I dont need all of the rows. Can i do this even with one row at a time.

For example: I have:
Table_1 (
a1 number,
a2 number,
....
a100 number
);

and

Table_2 (
a2 number,
a4 number,
...
a100 number
);

How can i do the following:
Insert into Table 2 
  Select * from Table_1
  where 'blah blah blah'


or can i do it? Smile

Thanks.

[Updated on: Wed, 13 June 2007 01:33]

Report message to a moderator

Re: Is there anything like that? [message #244488 is a reply to message #244479] Wed, 13 June 2007 02:00 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

Quote:
How can i do the following:

Insert into Table 2
Select * from Table_1
where 'blah blah blah'

or can i do it?





yes.


regards,
Re: Is there anything like that? [message #244492 is a reply to message #244488] Wed, 13 June 2007 02:15 Go to previous messageGo to next message
psix666
Messages: 51
Registered: April 2007
Location: Azerbaijan
Member

No!
Insert into Table 2 
  Select * from Table_1
  where 'blah blah blah'


This returns error "ora-00913: Too many values"

dhananjay just look for the table_1 and table_2 definition.
Re: Is there anything like that? [message #244493 is a reply to message #244492] Wed, 13 June 2007 02:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
List all the columns.
You should ALWAYS list all the columns.

Regards
Michel
Re: Is there anything like that? [message #244510 is a reply to message #244493] Wed, 13 June 2007 02:42 Go to previous messageGo to next message
psix666
Messages: 51
Registered: April 2007
Location: Azerbaijan
Member

Michel

I know that i should list all the columns. But i asking this becasue i need to do this many times. And tables in project always changes. It means that each time when I make changes to my tables i need to rebuild all my code. That's why i am looking for better solution. All this happens because i am now translating program from FoxPro to Oracle PL/SQL+Oracle Forms.

I can do this with dynamic sql, but i thing that may be something like that exists in Oracle PL/SQL, JAVA, SQL.

Thanks anyway.
Re: Is there anything like that? [message #244584 is a reply to message #244510] Wed, 13 June 2007 07:52 Go to previous message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
psix666 wrote on Wed, 13 June 2007 03:42
And tables in project always changes. It means that each time when I make changes to my tables i need to rebuild all my code.


Just a little bit of a note on this. Before you raise your blood pressure and tell me "well, that's the way it is," this is not a very good design at all. I'm sure you already knew that, but it sounds kooky to me.
Previous Topic: How to add a blank line between two paragraph?
Next Topic: collections
Goto Forum:
  


Current Time: Sat Dec 10 20:39:40 CST 2016

Total time taken to generate the page: 0.21943 seconds