check if data exist while inserting data

Question

How to insert a record to a table if there is no data exist of same type

insert into  user_table (userid, active,contid) values ('AAA',1,1);
insert into  user_table (userid, active,contid) values ('ABA',1,2);
          
        INSERT INTO new_table(userid,isactive)
SELECT userid,1
  FROM user_table where contid=1
 WHERE NOT EXISTS (SELECT userid
                     FROM new_table
                    WHERE contid=1
                  )

i need to copy data from one table1 to table2 if table2 doesnt have the same data. if data exist just skip and dont make any insert. I am getting "SQL command not properly ended" error with the above query

Please do not edit answers into your question that substantially changes the question and invalidates (some) existing answers. If you have clarifications to the question then keep the existing code and add a new section below with the clarification; or, if you have a follow-up question then [ask a new question](https://stackoverflow.com/questions/ask). — MT0, May 09 '23 at 07:47

score 2 · Answer 1 · answered May 09 '23 at 07:40

You can use a MERGE statement:

MERGE INTO new_table dst
USING ( SELECT userid, contid
        FROM   user_table
        WHERE  contid = 1) src
ON src.userid = dst.userid AND src.contid = dst.contid
WHEN NOT MATCHED THEN
  INSERT (userid, isactive) VALUES (src.userid, 1)

score 1 · Answer 2 · answered May 09 '23 at 07:21

Your insert query has two where keywords; the 2nd one should've been and:

INSERT INTO new_table (userid, isactive)
   SELECT userid, 1
     FROM user_table
    WHERE     contid = 1
          AND NOT EXISTS               --> AND instead of WHERE
                 (SELECT userid
                    FROM new_table
                   WHERE contid = 1);

Also, perhaps a minus set operator would do as well:

INSERT INTO new_table (userid, isactive)
   SELECT u.userid, 1
     FROM user_table u
   MINUS
   SELECT n.userid, 1
     FROM new_table n;

score 1 · Accepted Answer · answered May 09 '23 at 07:32

The exisrs clause should have another condition.

You need to check if a active userid already exists too

INSERT INTO new_table(userid,isactive)
SELECT userid,1
  FROM user_table WHERE contid=1
 AND NOT EXISTS (SELECT 1
                     FROM new_table
                    WHERE user_table.userid = new_table.userid AND contid=1
                  )

score 0 · Answer 4 · answered May 09 '23 at 07:26

0

You used the where keyword twice. I'm sharing the code below.

INSERT INTO new_table(userid,isactive)
SELECT userid,1 
FROM user_table 
WHERE contid=1 
AND NOT EXISTS (SELECT userid FROM new_table WHERE contid=1 )

answered May 09 '23 at 07:26

Onur T.

1
1

This fixes the syntax error but will only add the row when `new_table` contains no rows where `contid=1` regardless of the `userid`; it does not correlate the `EXISTS` sub-query to compare the `userid` values. – MT0 May 09 '23 at 07:42

score 0 · Answer 5 · answered May 09 '23 at 07:48

You can use left join to get only users that are not exists on table new_table using the condition new_table.userid is null :

INSERT INTO new_table(userid, isactive)
SELECT u.userid, 1
FROM user_table u
LEFT JOIN new_table n on n.userid = u.userid
where u.contid=1 and u.active = 1 and n.userid is null

check if data exist while inserting data

5 Answers5