CODEWITHSUNDEEP
sqloracle-databaseplsql
Arav
Arav

Reputation: 5247

Regarding sql query

I have the below sql query to insert the records from a table. The problem is when there is no records for select query i still want an record with the sequence number,null,'3' value inserted. Nothing get's inserted when there is no record found for select query. how can i do it?

insert into test_details(seqnbr,trepid,type)
select '&seqid'
      ,REP_ID
      ,'3'
  FROM ref_details
 WHERE REP_ID >13;

Upvotes: 1

Views: 85

Answers (2)

OMG Ponies
OMG Ponies

Reputation: 332731

Oracle 9i+:

To fill in gaps, you need to create a list of sequencial values:

INSERT INTO TEST_DETAILS
  (seqnbr, trpid, type)
 SELECT '&seqid', rd.rep_id, '3'
     FROM (SELECT LEVEL + 13
             FROM DUAL
       CONNECT BY LEVEL <= 13) x
LEFT JOIN REF_DETAILS rd ON rd.rep_id = x.level
                        AND rd.rep_id > 13

...then LEFT JOIN to the table that can have gaps.

Upvotes: 3

josephj1989
josephj1989

Reputation: 9709

One way would be

insert into test_details(seqnbr,trpid,type)
select '&seqid',rep_id,'3' from ref_details where rep_id>13
union all select '&seqid',null,'3'
from dual where not exists(
select 1 from ref_details where rep_id>13)

Upvotes: 4

Related Questions