目录
2.在一条语句中执行INSERT、UPDATE和DELETE
1.使用TOP分块修改数据
use AdventureWorks
go
--创建一个示例删除表
SELECt *
INTO Production.Example_BillOfMaterials
FROM Production.BillOfMaterials
--所有行以500行为一块进行删除
WHILE (SELECT COUNT(*) FROM Production.Example_BillOfMaterials)>0
BEGIN
DELETE TOP(500)
FROM Production.Example_BillOfMaterials
END
这种“分块”的方法也能用于INSERTB、UPDATE。对于INSERT和UPDATE,TOP子句就跟在INSERT和UPDATE
关键字后面,例如;
INSERT TOP(100)
UPDATE TOP(25)
2.在一条语句中执行INSERT、UPDATE和DELETE
可以不用创建多条数据修改语句,而只使用一个IHERGE到目标或源袭,定义当搜索条件找到匹配、当目标表没有匹配时或当源表没有匹配时执行什么操作.基于这些匹配条件,你可以指定是否执行DELETE、INSERT或UPDATE操作(再次说明是在同一语句中)。
use AdventureWorks
go
--创建一个产品表
CREATE TABLE HumanResources.CorporateHousing
(
CorporateHousingID int NOT NULL PRIMARY KEY IDENTITY(1,1),
UnitNBR int NOT NULL,
IsRentedIND bit NOT NULL,
ModifedDate datetime NOT NULL DEFAULT GETDATE()
)
GO
--插入现有的单位
INSERT HumanResources.CorporateHousing
(UnitNBR,IsRentedIND)
VALUES
(1,0),
(24,1),
(39,0),
(54,1)
GO
--把最新的信息添加到表中
CREATE TABLE dbo.StagingCorporateHousing
(
UnitNBR int NOT NULL,
IsRentedIND bit NULL
)
GO
INSERT dbo.StagingCorporateHousing
(UnitNBR,IsRentedIND)
VALUES
--UnitNBR"1"不再存在
(24,0),
(39,1),
(54,0),
(92,1)
--修改之前,查看产品的值
SELECT CorporateHousingID,UnitNBR,IsRentedIND
FROM HumanResources.Corporatehousing
use AdventureWorks
go
MERGE INTO HumanResources.CorporateHousing p
USING dbo.stagingCorporateHousing s
ON p.unitNBR=s.unitNBR
WHEN MATCHED AND s.IsRentedIND<>p.IsRentedIND THEN
UPDATE SET IsRentedIND=s.IsRentedIND
WHEN NOT MATCHED BY TARGET THEN
INSERT (UnitNBR,IsRentedIND) VALUES (s.UnitNBR,s.IsRentedIND)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
SELECT CorporateHousingID,UnitNBR,IsRentedIND
FROM HumanResources.corporateHousing