Skip insert in SQL if duplicate exists.

earwig75
earwig75 used Ask the Experts™
on
I want to skip or ignore an insert if the values already exist in the table. Below is the SQL I am trying to use but it doesn't seem to work. Can someone assist? I am filling in the values, but they are normally entered by variables in my application and this query is in a loop... that's why I want to ignore if a duplicate exists. Thank you.

INSERT INTO myTable
                  ([ID],[theName],[Auth],[LastModifiedBy])
            VALUES(
                  5
                  ,hello
                  ,yes
                  ,bob
                  )      
            WHERE hello
            NOT IN      (SELECT [ID], [hello]
                         FROM myTable
                         WHERE ID = 5
                         AND AccessID = hello
                  )
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
<air code>  If you're inserting static values, you can use EXISTS to determine if the row exists, and insert if it is not.
IF NOT EXISTS (SELECT SELECT ID, hello FROM myTable WHERE ID = 5 AND AccessID = 'hello') 
   INSERT INTO myTable (ID, theName, Auth, LastModifiedBy) 
   VALUES (5 ,'hello' ,'yes' ,'bob')      

Open in new window

Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
INSERT INTO myTable
                  ([ID],[theName],[Auth],[LastModifiedBy])
            SELECT ID, AccessID, Auth, LastModifiedBy
            FROM (
                  SELECT 5 AS ID
                  ,'hello' AS AccessID
                  ,'yes' AS Auth
                  ,'bob' AS LastModifiedBy
            ) AS new_data
            WHERE NOT EXISTS (
                SELECT 1
                FROM myTable mt
                WHERE
                    mt.ID = new_data.ID AND
                    mt.theName = new_data.AccessID )

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial