Home » SQL & PL/SQL » SQL & PL/SQL » insert into table A with Select query on B and C (Oracle 10g)
insert into table A with Select query on B and C [message #580266] Fri, 22 March 2013 02:29 Go to next message
sbejuga
Messages: 1
Registered: March 2013
Junior Member
Hi,

I have a table A on dev with definition as TAble A(address,name) and the same table on Prod is defined as Table A(name,address).

my question is Ihave one package in that am trying to insert into this table as follows:

INSERT INTO A
SELECT b.name name,
a.address address,
FROM smaple b, sample a
WHERE b.classid = 'class'
AND b.CIS_MEMS_APPNAME = a.name(+)

so the query works on Prod but fails on Dev because column order is different.

I have 2 solutions:

1. I can mention column names in insert line and modify the query but tomorro some body changes again the definition of table A I need to change the query, so do I have solution in oracle sql that can handle the column order without specifying the column names in insert line.

so tomorrow On prod column order and on Dev column order is different though my sql should successfully execute.

I appreciate your support.

regards
satya.
Re: insert into table A with Select query on B and C [message #580267 is a reply to message #580266] Fri, 22 March 2013 02:35 Go to previous messageGo to next message
Littlefoot
Messages: 18846
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
I can mention column names in insert line and modify the query

That would be a good idea.

Quote:
but tomorro some body changes again the definition of table A I need to change the query

That happens; tables aren't carved in stone and sometimes they change. Which means that code that does *something* with these tables changes as well.
Re: insert into table A with Select query on B and C [message #580283 is a reply to message #580267] Fri, 22 March 2013 04:27 Go to previous messageGo to next message
zssultana
Messages: 3
Registered: March 2013
Junior Member

YOU HAVE TO CHANGE YOUR CODE ACCORDING TO THE TABLE DEFINITION...
Re: insert into table A with Select query on B and C [message #580288 is a reply to message #580283] Fri, 22 March 2013 04:38 Go to previous messageGo to next message
Michel Cadot
Messages: 57648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 22 March 2013 10:37
Please read OraFAQ Forum Guide and do NOT post in UPPER case.
Posting all in UPPER case means shouting.

In addition, this is what we already said, no need you repeat it.

Regards
Michel

Re: insert into table A with Select query on B and C [message #580289 is a reply to message #580283] Fri, 22 March 2013 04:38 Go to previous messageGo to next message
cookiemonster
Messages: 10593
Registered: September 2008
Location: Rainy Manchester
Senior Member
@zssultana - you have to stop posting in upper case. It is considered shouting on all internet forums.
The OP needs to specify the column names in the insert as already mentioned.
Re: insert into table A with Select query on B and C [message #580292 is a reply to message #580289] Fri, 22 March 2013 05:21 Go to previous message
dariyoosh
Messages: 513
Registered: March 2009
Location: Iran / France
Senior Member
cookiemonster wrote on Fri, 22 March 2013 10:38
... you have to stop posting in upper case. It is considered shouting on all internet forums ...


Actually if it is bold and underlined (which was not the case in his post), it could also be interpreted (maybe not in this forum) as emphasizing on an argument in a discussion in order to show its importance.

But generally speaking, yes, you're right, it is often considered as shouting, so it's good to use some smilies in order to give a good impression !! Laughing

And of course it has not to be used here according to forum guidelines !

Regards,
Dariyoosh

[Updated on: Fri, 22 March 2013 05:23]

Report message to a moderator

Previous Topic: PLS-00103 Error while compiling a package
Next Topic: Can anyone give me running pl/sql code calling a webservice
Goto Forum:
  


Current Time: Thu Apr 24 14:01:31 CDT 2014

Total time taken to generate the page: 0.16934 seconds