CODEWITHSUNDEEP
sqlsql-server
chink
chink

Reputation: 1653

How to check and insert values only if it doesn't exist in the table for sql

I have a table in which I am trying to insert data based on few conditions, and insert only if the data is not already present in the data I have written a query to insert data

INSERT INTO #Tbl_Output(SId)
        SELECT sdc.LId
        FROM #Tbl_DIds_Input di
            INNER JOIN sdc
                ON di.Id = sdc.Id
        WHERE sdc.Active = 'Y' AND sdc.LId IS NOT NULL
        GROUP BY sdc.LId

For this I want to add a check and insert only those sdc.LId in the Tbl_Output table only if they are not already present in the table. How do I add this check to this

Upvotes: 0

Views: 703

Answers (3)

Venkataraman R
Venkataraman R

Reputation: 13009

In addition to other answers, You can also go for MERGE statement.

MERGE INTO #Tbl_Output as target
USING (SELECT sdc.LId
        FROM #Tbl_DIds_Input di
            INNER JOIN sdc
                ON di.Id = sdc.Id
        WHERE sdc.Active = 'Y' AND sdc.LId IS NOT NULL
        GROUP BY sdc.LId) AS source
ON target.SId = source.LId
WHEN NOT MATCHED THEN
 INSERT (sId)  
        VALUES (source.LId)

Upvotes: 0

gotqn
gotqn

Reputation: 43666

Try this:

INSERT INTO #Tbl_Output(SId)
    SELECT sdc.LId
    FROM #Tbl_DIds_Input di
        INNER JOIN sdc
            ON di.Id = sdc.Id
    WHERE sdc.Active = 'Y' AND sdc.LId IS NOT NULL
       AND sdc.LId NOT IN (SELECT SiD FROM #Tbl_Output)
    GROUP BY sdc.LId

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270873

You can use not exists:

INSERT INTO #Tbl_Output(SId)
    SELECT sdc.LId
    FROM #Tbl_DIds_Input di JOIN
         sdc
         ON di.Id = sdc.Id
    WHERE sdc.Active = 'Y' AND sdc.LId IS NOT NULL AND
          NOT EXISTS (SELECT 1 FROM #Tbl_Output o WHERE o.SId = sdc.LId)
    GROUP BY sdc.LId;

Upvotes: 1

Related Questions