Home » SQL & PL/SQL » SQL & PL/SQL » Insert-select query problem..
Insert-select query problem.. [message #257326] Wed, 08 August 2007 01:48 Go to next message
fortunethiyagu
Messages: 94
Registered: December 2006
Member

Hi,

Query:


insert into sample_tab_1
	( gen_id,
	  gen_Name
	)
select
	gen_id_v1 || gen_id_v2
	'IRGTD'
from 
	gen_table
where
	( (gen_id_v1 is not null ) OR
	  (gen_id_v2 is not null )
	);


In above query gen_id_v1 or gen_id_v2 might come,
if both are not null then both the values are
concatenated and inserted.


Now i want to change query that insert two different
records if both are not null. That is if gen_id_v1 &
gen_id_v2 both are not null then i have to insert two seperate
records.

(ex):

gen_id_v1_x,'IRGTD' as 1st record
gen_id_v2_x,'IRGTD' as 2nd record


How to modify this query such the way it inserts two seperate
records?

Please help me...
Re: Insert-select query problem.. [message #257329 is a reply to message #257326] Wed, 08 August 2007 01:56 Go to previous messageGo to next message
jheronimus
Messages: 11
Registered: March 2007
Location: The Netherlands, Groninge...
Junior Member
try this:
insert into sample_tab_1
   ( gen_id,
     gen_Name
   )
select
    gen_id_v1 
,   'IRGTD'
from 
   gen_table
where
   ( (gen_id_v1 is not null ) OR
     (gen_id_v2 is not null )
   )
union
select
     gen_id_v2
,   'IRGTD'
from 
   gen_table
where
   ( (gen_id_v1 is not null ) OR
     (gen_id_v2 is not null )
   )
   ;


Greetz,
Jeroen de Jong

[Updated on: Wed, 08 August 2007 01:58]

Report message to a moderator

Re: Insert-select query problem.. [message #257335 is a reply to message #257329] Wed, 08 August 2007 02:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
UNION ALL instead of UNION unless you want to remove duplicates.

Regards
Michel
Re: Insert-select query problem.. [message #257364 is a reply to message #257329] Wed, 08 August 2007 03:29 Go to previous messageGo to next message
fortunethiyagu
Messages: 94
Registered: December 2006
Member

Hi,

Is this the only way to do or any other kind of things can be done? Bcoz iam having lot many queries....

Is it possible to make a single query?

-fortunethiyagu

[Updated on: Wed, 08 August 2007 03:30]

Report message to a moderator

Re: Insert-select query problem.. [message #257385 is a reply to message #257364] Wed, 08 August 2007 04:09 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is a single statement.

Regards
Michel
Previous Topic: UTL
Next Topic: get max value.
Goto Forum:
  


Current Time: Sun Dec 04 10:55:11 CST 2016

Total time taken to generate the page: 0.08468 seconds