Combining NOT EXISTS with INSERT ... SELECT

Question

The following code inserts a new row into a table based on a value in another row but it also allows duplicate data. I want to use NOT EXISTS so that if there is already a row with that value then it won't insert another one, but not sure how to integrate this.

INSERT INTO [Grading].[dbo].[tblObservations]
       ([FormID]
       ,[Data]
       ,[UserID]
       ,[DateOfObservation]
       ,[Final]
       ,[ValidTo]
       ,[ID_Grading]
       ,[ID_ObservationKind]
       ,[Created]
       ,[Modified]
       ,[RowVersion])
SELECT [FormID]
  ,'0'
  ,[UserID]
  ,[DateOfObservation]
  ,[Final]
  ,[ValidTo]
  ,[ID_Grading]
  ,40
  ,[Created]
  ,[Modified]
  ,[RowVersion]
  FROM [Grading].[dbo].[tblObservations]
  WHERE [ID_ObservationKind] = 39 AND [Data] = 'No' AND [Final] = 1

Any help appreciated.

possible duplicate of [Only inserting a row if it's not already there](http://stackoverflow.com/questions/3407857/only-inserting-a-row-if-its-not-already-there) — Martin Smith, Apr 23 '13 at 07:26

score 1 · Answer 1 · answered Apr 23 '13 at 07:26

1

You can try

IF NOT EXISTS
    (
    SELECT 1
FROM [Grading].[dbo].[tblObservations]
WHERE [ID_ObservationKind] = 39 AND [Data] = 'No' AND [Final] = 1
    )

    BEGIN
        -- Insert script
    END

answered Apr 23 '13 at 07:26

Satpal

132,252
13
159
168

Without additional locking hints or a unique constraint this can allow dupes under load. – Martin Smith Apr 23 '13 at 07:27
@Martin Smith, completely agree with you – Satpal Apr 23 '13 at 07:28

score 1 · Accepted Answer · answered Apr 23 '13 at 07:31

Try this one -

USE [Grading]

INSERT INTO [dbo].[tblObservations]
(
      FormID
    , Data
    , UserID
    , DateOfObservation
    , Final
    , ValidTo
    , ID_Grading
    , ID_ObservationKind
    , Created
    , Modified
    , [RowVersion]
)
SELECT 
      t.FormID
    , '0'
    , t.UserID
    , t.DateOfObservation
    , t.Final
    , t.ValidTo
    , t.ID_Grading
    , 40
    , t.Created
    , t.Modified
    , t.[RowVersion]
FROM dbo.tblObservations t
LEFT JOIN dbo.tblObservations t2 ON 
        t.FormID = t2.FormID
    AND t2.Data = '0'
    AND t.UserID = t2.UserID
    AND t.DateOfObservation = t2.DateOfObservation
    AND t.Final = t2.Final
    AND t.ValidTo = t2.ValidTo
    AND t.ID_Grading = t2.ID_Grading
    AND t2.ID_ObservationKind = 40
    AND t.Created = t2.Created
    AND t.Modified = t2.Modified
    AND t.[RowVersion] = t2.[RowVersion]
WHERE t.ID_ObservationKind = 39 
    AND t.Data = 'No' 
    AND t.Final = 1
    AND t2.FormID IS NULL

Or try this -

INSERT INTO [dbo].[tblObservations]
(
      FormID
    , Data
    , UserID
    , DateOfObservation
    , Final
    , ValidTo
    , ID_Grading
    , ID_ObservationKind
    , Created
    , Modified
    , [RowVersion]
)
SELECT 
      t.FormID
    , '0'
    , t.UserID
    , t.DateOfObservation
    , t.Final
    , t.ValidTo
    , t.ID_Grading
    , 40
    , t.Created
    , t.Modified
    , t.[RowVersion]
FROM dbo.tblObservations t
WHERE t.ID_ObservationKind = 39 
    AND t.Data = 'No' 
    AND t.Final = 1
    AND NOT EXISTS(
        SELECT 1 
        FROM dbo.tblObservations t2
        WHERE t2.ID_ObservationKind = 39 
            AND t2.Data = 'No' 
            AND t2.Final = 1
    )

The INSERT with the LEFT JOIN worked perfectly for me, many thanks Devart. — user2165345, Apr 29 '13 at 19:59

score 0 · Answer 3 · answered Apr 23 '13 at 07:27

0

You're looking to do a "merge" or "upsert" in SQL. Depending on your database, there are a number of ways to do this. If you are using MySQL, you'll want to use it's INSERT... ON DUPLICATE KEY UPDATE syntax.

answered Apr 23 '13 at 07:27

Tim Dorr

4,861
2
21
24

Combining NOT EXISTS with INSERT ... SELECT

3 Answers3