SQL Insert Only If Row is Not Present

Question

I have the following tables with data.

Table_1

ID, GROUP_ID, ARTIFACT_ID, VERSION  
101, com.abc, pqra, 1.0.0  
102, com.abc, pqrb, 2.0.0  
103, com.abc, pqrc, 3.0.0  
104, com.abc, pqrd, 4.0.0

Table_2

ID, MODULE_ID, ISSUE_KEY  
11, 104, XYZ-12  
12, 104, XYZ-34

I am using the following command to insert values in Table_2.

INSERT INTO Table_2 (MODULE_ID, ISSUE_KEY) SELECT ID, 'XYZ-56' FROM Table_1
WHERE Table_1.GROUP_ID = 'com.abc' AND Table_1.ARTIFACT_ID = 'pqrd' AND 
Table_1.VERSION = '4.0.0'

As you can infer from the above query, I am first trying to get the ID from TABLE_1 for a given GROUP_ID, ARTIFACT_ID and VERSION value and then inserting data into TABLE_2 with the retrieved ID and ISSUE_KEY value as XYZ-56. After executing the above command, the Table_2 will look like

ID, MODULE_ID, ISSUE_KEY  
11, 104, XYZ-12  
12, 104, XYZ-34
13, 104, XYZ-56

The problem with the above INSERT query is that it will not check whether there already exists a given row with a certain value of MODULE_ID and ISSUE_KEY. For example, If I execute the same insert query again, then the table will look like

ID, MODULE_ID, ISSUE_KEY  
11, 104, XYZ-12  
12, 104, XYZ-34
13, 104, XYZ-56
14, 104, XYZ-56

I want the row to be inserted in Table_2 only of there is no row already present with a given MODULE_ID and ISSUE_KEY.
Can someone help me modify the above insert query to do as expected.

use not exists like here http://stackoverflow.com/a/41186684/2562699 — whereisSQL, Feb 13 '17 at 20:47

score 1 · Answer 1 · answered Feb 13 '17 at 20:46

You could do it with a WHERE NOT EXISTS:

Insert  Into Table_2 (MODULE_ID, ISSUE_KEY)
Select  ID, 'XYZ-56'
From    Table_1 T1
Where   T1.GROUP_ID = 'com.abc'
And     T1.ARTIFACT_ID = 'pqrd'
And     T1.VERSION = '4.0.0'
And Not Exists
(
    Select  *
    From    Table_2 T2
    Where   T2.Module_Id = T1.Id
    And     T2.Issue_Key = 'XYZ-56'
);

Here's another method using a CTE and a LEFT JOIN:

;With ToInsert As
(
    Select  ID As Module_Id, 'XYZ-56' As Issue_Key
    From    Table_1 T1
    Where   T1.GROUP_ID = 'com.abc'
    And     T1.ARTIFACT_ID = 'pqrd'
    And     T1.VERSION = '4.0.0'
)
Insert      Table_2
            (Module_Id, Issue_Key)
Select      I.Module_Id, I.Issue_Key
From        ToInsert    I
Left Join   Table_2     T2  On  T2.Module_Id = I.Module_Id
                            And T2.Issue_Key = I.Issue_Key
Where       T2.Id Is Null;

score 0 · Answer 2 · answered Feb 13 '17 at 20:49

You can use an if block like below to check whether a record exists or not:

IF NOT EXISTS (SELECT * FROM Table_2 WHERE ISSUE_KEY = 'XYZ-56')
BEGIN

INSERT INTO Table_2 (MODULE_ID, ISSUE_KEY) SELECT ID, 'XYZ-56' FROM Table_1
WHERE Table_1.GROUP_ID = 'com.abc' AND Table_1.ARTIFACT_ID = 'pqrd' AND 
Table_1.VERSION = '4.0.0'

END

score 0 · Answer 3 · answered Feb 13 '17 at 20:54

0

You could use except:

INSERT INTO Table_2 (MODULE_ID, ISSUE_KEY) 
SELECT ID, 'XYZ-56' 
FROM Table_1
WHERE Table_1.GROUP_ID = 'com.abc' 
  AND Table_1.ARTIFACT_ID = 'pqrd' 
  AND Table_1.VERSION = '4.0.0'  

except 
select MODULE_ID, ISSUE_KEY
from Table_2

or not exists():

INSERT INTO Table_2 (MODULE_ID, ISSUE_KEY) 
SELECT ID, 'XYZ-56' 
FROM Table_1
WHERE Table_1.GROUP_ID = 'com.abc' 
  AND Table_1.ARTIFACT_ID = 'pqrd' 
  AND Table_1.VERSION = '4.0.0'  
  and not exists (
    select 1
    from table_2 as i
    where i.Module_ID = table_1.ID
      and i.Issue_Key = 'XYZ-56' 
  )

or other similar methods.

answered Feb 13 '17 at 20:54

SqlZim

37,248
6
41
59

Although the it might be desirable, using `except` also removes duplicates. This also suggests that it does additional, unnecessary processing. – Gordon Linoff Feb 13 '17 at 20:58
@GordonLinoff That's true, it does remove duplicates. It also handles `null` values differently when checking for equality. – SqlZim Feb 13 '17 at 21:04

score 0 · Answer 4 · answered Feb 13 '17 at 21:05

You are looking for Merge statement.

Quoting docs:

Performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.

Note: read performance tips carefully.

SQL Insert Only If Row is Not Present

4 Answers4