Skip insert in SQL if duplicate exists.
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],[La
VALUES(
5
,hello
,yes
,bob
)
WHERE hello
NOT IN (SELECT [ID], [hello]
FROM myTable
WHERE ID = 5
AND AccessID = hello
)
INSERT INTO myTable
([ID],[theName],[Auth],[La
VALUES(
5
,hello
,yes
,bob
)
WHERE hello
NOT IN (SELECT [ID], [hello]
FROM myTable
WHERE ID = 5
AND AccessID = hello
)
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
<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')
INSERT INTO myTable
([ID],[theName],[Auth],[La
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 )
([ID],[theName],[Auth],[La
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 )
Premium Content
You need an Expert Office subscription to comment.Start Free Trial