DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
 
 
SITE SEARCH SQLUSA.com VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
How to synchronize tables with correlated subquery?

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
 

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

Exam Prep 70-461
SQL 2016 DESIGN & PROGRAMMING
 
 
 
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE