Execute the following T-SQL example scripts in Microsoft SQL Server Management Studio Query Editor to demonstrate the delete of records in a table which are not in another table.

-- SQL select into table create for test data
USE tempdb;
SELECT SalesOrderID = CONVERT(INT,SalesOrderID),
       OrderDate,
       TotalDue
INTO   SOH
FROM   AdventureWorks.Sales.SalesOrderHeader
GO
-- (31465 row(s) affected)
 
-- SQL insert - add extra records with non-matching SalesOrderID-s
-- MSSQL insert select - T-SQL top function
INSERT SOH
SELECT TOP 1000 SalesOrderID + 100000,
                OrderDate + 2,
                TotalDue + 1000
FROM   AdventureWorks.Sales.SalesOrderHeader
GO
-- (1000 row(s) affected)
 
-- SQL delete table - delete non-matching records
DELETE x
FROM   SOH AS x
WHERE  NOT EXISTS (SELECT 1
                   FROM   AdventureWorks.Sales.SalesOrderHeader y
                   WHERE  x.SalesOrderID = y.SalesOrderID)
GO
-- (1000 row(s) affected)

 
-- SQL alternate delete syntax
-- MSSQL delete table - delete non-matching records
DELETE SOH
WHERE  NOT EXISTS (SELECT *
                   FROM   AdventureWorks.Sales.SalesOrderHeader y
                   WHERE  SOH.SalesOrderID = y.SalesOrderID)
GO

 
 
-- Cleanup
DROP TABLE SOH

GO
 

------------