Insert records in SQL if records does not exists

Question

I would like to insert records in SQL such that if the combination of entry exists then the script should not proceed with the insert statement. here is what i have so far :

insert into TABLE_TESTING(R_COMPONENT_ID,OPRID) 
select 1942,'Test' 
from  TABLE_TESTING 
where not exists 
    (select * 
     from TABLE_TESTING 
     where R_COMPONENT_ID='1942' 
       and oprid ='Test');

I have a table name as : TABLE_TESTING It has two columns as : R_COMPONENT_ID and OPRID

If the combination of record as '1942' and 'Test' already exist in DB then my script should not perform insert operation and if it doesent exists then it should insert the record as a combination of R_COMPONENT_ID and OPRID.

Please suggest. Using the query specified above i am getting multiple insert been added in the DB. Please suggest some solution.

Which DBMS are you using? Postgres? Oracle? – Mar 03 '14 at 07:33 — , Mar 03 '14 at 07:33
@ a_horse_with_no_name I am using Oracle. – user3373799 Mar 03 '14 at 07:41 — user3373799, Mar 03 '14 at 07:41

score 3 · Answer 1 · answered Mar 03 '14 at 08:12

As you don't want to update existing rows, your approach is essentially correct. The only change you have to do, is to replace the from table_testing in the source of the insert statement:

insert into TABLE_TESTING (R_COMPONENT_ID,OPRID) 
select 1942,'Test' 
from  dual -- <<< this is the change
where not exists 
    (select * 
     from TABLE_TESTING 
     where R_COMPONENT_ID = 1942
       and oprid = 'Test');

When you use from table_testing this means that the insert tries to insert one row for each row in TABLE_TESTING. But you only want to insert a single row. Selecting from DUAL will achieve exactly that.

As others have pointed out, you can also use the MERGE statement for this which might be a bit better if you need to insert more than just a single row.

merge into table_testing target
using 
(
    select 1942 as R_COMPONENT_ID, 'Test' as OPRID from dual
    union all 
    select 1943, 'Test2' from dual
) src
ON (src.r_component_id = target.r_component_id and src.oprid = target.oprid)
when not matched
then insert (r_component_id, oprid)
     values (src.r_component_id, src.oprid);

score 2 · Answer 2 · answered Mar 03 '14 at 06:57

2

Try This one

if not exists(Select * From TABLE_TESTING where R_COMPONENT_ID='1942' and OPRID='Test' )
begin
    insert into TABLE_TESTING(R_COMPONENT_ID,OPRID) values('1942','Test')
end

answered Mar 03 '14 at 06:57

joker

982
9
23

Its saying error starting in command. here is teh query i am executing : if not exists(Select * From TABLE_TESTING where R_COMPONENT_ID='1942' and OPRID='Test' ) begin insert into TABLE_TESTING(R_COMPONENT_ID,OPRID) values('1942','Test') end; – user3373799 Mar 03 '14 at 07:03
i executed this in my Oracle SQL Developer – user3373799 Mar 03 '14 at 07:07
It use in SQL SERVER, dnt know about Oracle SQL Developer. – joker Mar 03 '14 at 07:09
I mean SQL Server only.Please suggest. – user3373799 Mar 03 '14 at 07:11
yes my ans is work in sql server, dnt knw about OSD. For oracle sql developer ref this [link] (http://stackoverflow.com/questions/10824764/oracle-insert-if-not-exists-statement) – joker Mar 03 '14 at 07:13

score 0 · Answer 3 · answered Mar 03 '14 at 06:59

0

You can make use of MERGE operation.

answered Mar 03 '14 at 06:59

Krishna Mohan

1,612
3
19
27

As I am new to SQL hence dont have much knowledge. – user3373799 Mar 03 '14 at 07:06
It is better to gain some knowledge, than just copy-pasting. Just a suggestion. Cool. – Krishna Mohan Mar 03 '14 at 08:07

score 0 · Answer 4 · answered Mar 03 '14 at 07:30

0

insert into TABLE_TESTING  
select 1942,'Test' where 0<(
select count(1) from TABLE_TESTING 
where not exists(select 1 from TABLE_TESTING  where R_COMPONENT_ID=1942 and oprid ='Test'))

Try the above code.

answered Mar 03 '14 at 07:30

Ramya

230
2
9

getting the following error code : SQL Error: ORA-00923: FROM keyword not found where expected 00923. 00000 - "FROM keyword not found where expected" – user3373799 Mar 03 '14 at 07:34

score 0 · Accepted Answer · answered Mar 03 '14 at 07:41

0

Here is an skelton to use MERGE. I ran it and it works fine. You may tweak it further per your needs. Hope this helps!

DECLARE
BEGIN
   FOR CURTESTING IN (SELECT R_COMPONENT_ID, OPRID FROM TABLE_TESTING)
   LOOP
      MERGE INTO TABLE_TESTING
           USING DUAL
              ON (R_COMPONENT_ID = '1942' AND OPRID = 'Test')
      WHEN NOT MATCHED
      THEN
         INSERT     (PK, R_COMPONENT_ID, OPRID)
             VALUES (TEST_TABLE.NEXTVAL, '1942', 'Test');
   END LOOP;

   COMMIT;
END;

answered Mar 03 '14 at 07:41

Ram Dwivedi

470
3
11

Doing a merge in a loop is not such a good idea. This can be done without using (a terribly slow) cursor approach – Mar 03 '14 at 07:49
I agree. Idea is to give skelton of MERGE as @user3373799 mentioned unawareness about MERGE. – Ram Dwivedi Mar 03 '14 at 07:55

Insert records in SQL if records does not exists

5 Answers5