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;
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)
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.
LEVEL is an incrementing value for reference in Oracle's CONNECT BY syntax for recursive queries. The value will start at 1, so the query adds 13 to start at the minimum value you specified in your question. The link provides examples of how to set the upper limit to be based on a table value...