In this example, I create a temp table on AdventureWorks2012 Database, and first I load all the data in HumanResources.Employee to the temporary table. Then I delete data using HumanResources.Employee. This command will delete all the records in Tmp which has Gender = ‘M’ in HumanResources.Employee table.
nn
USE [AdventureWorks2012]nGOnn--Inserting data from HumanResources.Employee and Person.Person to non existing tablenSELECT *n INTO Tmpn FROM [HumanResources].[Employee] nGOnn--DELETE Using another tablenDELETE Tmp n FROM [HumanResources].[Employee] AS HE, Tmpn WHERE Tmp.BusinessEntityID = HE.BusinessEntityIDn AND HE.Gender = 'M'nn--Retrieving datanSELECT * FROM TmpnGOnn--Dropping TablenDROP TABLE TmpnGOnnn
You can use an inner or outer join to combine those data.
nn
USE [AdventureWorks2012]nGOnn--Inserting data from HumanResources.Employee and Person.Person to non existing tablenSELECT *n INTO Tmpn FROM [HumanResources].[Employee] nGOnn--DELETE Using another tablenDELETE Tmp n FROM [HumanResources].[Employee] AS HEn INNER JOIN Tmpn ON Tmp.BusinessEntityID = HE.BusinessEntityIDn WHERE HE.Gender = 'M'nn--Retrieving datanSELECT * FROM TmpnGOnn--Dropping TablenDROP TABLE TmpnGOnnn
Tags
nn
- n
- SQL
- DELETE
- T-SQL
- TSQL
- SQL Server
n
n
n
n
n
n