Insert Only Records That Do Not Exist

Question

I had two people performing data entry into Excel, and I imported the csv files into MS SQL Server. Now as a sanity check and triple check, I want to insert records from [dbo].[SSOne] into [dbo].[SaleInformation] that DO NOT EXIST already in [dbo].[SaleInformation] - the column to check is OrderNumber. If the OrderNumber exists in both [SSOne] and [SaleInformation] do not insert the data. If the OrderNumber only exists in [SSOne] and not in [SaleInformation] then insert the row.

How would I achieve this? SSMS 2016

Answer 1

Hi @Mitch McConnell ,

You can use a MERGE statement for your scenario.
Here is a conceptual example for you.

SQL

-- DDL and sample data population, start  
--Create a target table  
DECLARE @Products TABLE (  
   ProductID INT PRIMARY KEY,  
   ProductName VARCHAR(100),  
   Rate MONEY  
);  
  
INSERT INTO @Products  
VALUES (1, 'Tea', 10.00),  
   (2, 'Coffee', 20.00),  
   (3, 'Muffin', 30.00),  
   (4, 'Biscuit', 40.00);  
  
--Create source table  
DECLARE @UpdatedProducts TABLE (  
   ProductID INT PRIMARY KEY,  
   ProductName VARCHAR(100),  
   Rate MONEY  
);  
  
INSERT INTO @UpdatedProducts  
VALUES   (1, 'Tea', 10.00),  
   (2, 'Coffee', 25.00),  
   (3, 'Muffin', 35.00),  
   (5, 'Pizza', 60.00);  
-- DDL and sample data population, end  
  
--SELECT * FROM @Products;  
--SELECT * FROM @UpdatedProducts;  
  
  
--MERGE SQL statement  
--Synchronize the target table with  
--refreshed data from source table  
;MERGE INTO @Products /* WITH (UpdLock, HoldLock) */ AS TARGET    
USING @UpdatedProducts AS SOURCE   
ON (TARGET.ProductID = SOURCE.ProductID)   
-- When rows are matched, update the rows if there is any change  
-- but only if something needs to be updated!!!  
WHEN MATCHED AND (TARGET.ProductName <> SOURCE.ProductName   
   OR TARGET.Rate <> SOURCE.Rate) THEN   
   UPDATE SET TARGET.ProductName = SOURCE.ProductName,   
         TARGET.Rate = SOURCE.Rate  
--When no rows are matched, insert the incoming rows from source  
--table to target table  
WHEN NOT MATCHED BY TARGET THEN   
   INSERT (ProductID, ProductName, Rate)   
   VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)  
--When there is a row that exists in target table and  
--same row does not exist in source table  
--then delete this row from target table  
WHEN NOT MATCHED BY SOURCE THEN   
   DELETE  
--$action specifies a column of type nvarchar(10)   
--in the OUTPUT clause that returns one of three   
--values for each row: 'INSERT', 'UPDATE', or 'DELETE',   
--according to the action that was performed on that row  
OUTPUT $action,   
   DELETED.ProductID AS TargetProductID,   
   DELETED.ProductName AS TargetProductName,   
   DELETED.Rate AS TargetRate,   
   INSERTED.ProductID AS SourceProductID,   
   INSERTED.ProductName AS SourceProductName,   
   INSERTED.Rate AS SourceRate;  
-- INTO #log (a, dt, what);  
  
/*  
We can see the results:  
- Coffee rate was updated from 20.00 to 25.00,   
- Muffin rate was updated from 30.00 to 35.00,   
- Biscuit was deleted   
- Pizza was inserted.  
*/  
SELECT * FROM @Products;  

Answer 2

One way is to use an OUTER (LEFT) JOIN to validate the OrderNumber don't exists in SalesInformation

-- insert into 
select T1.*
from SSOne as T1
     left join
     SaleInformation as T2
         on T1.OrderNumber = T2.OrderNumber
where T2.OrderNumber is null

Answer 3

The normal way would be to use NOT EXISTS:

INSERT tbl (....)
   SELECT ....
   FROM   source s
   WHERE NOT EXISTS (SELECT *
                    FROM  tbl t
                    WHERE t.OrderNumber = s.OrderNumber)

Answer 4

Hi，@Mitch McConnell
The key to this issue is to find data that is in the source table but not in the target table.
Generally, the first we think of is the "not in" statement:

 SELECT S.* FROM [dbo].[SSOne] S   
 WHERE S.OrderNumber NOT IN (SELECT T.OrderNumber FROM [dbo].[SaleInformation] T)    

But if the target table is very long, to execute the above query statement, you need to use the fields in the source table to match each field in the target table.
It means to traversing the target table for each field of the source table, which is very inefficient.
So,we can use join query which is more efficient like this:

 SELECT * FROM   
 [dbo].[SSOne] A LEFT JOIN [dbo].[SaleInformation] B  
 ON A.OrderNumber = B.OrderNumber  

In this way, the B table will be filled with null.Then add the condition WHERE B.OrderNumber IS NULL in the query statement,that's what we need.

We can also use EXCEPT to get the difference between the two tables,like this:

 SELECT S.OrderNumber FROM [dbo].[SSOne] S  
 EXCEPT  
 SELECT T.OrderNumber FROM [dbo].[SaleInformation] T  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.