目录
1.使用TOP分块修改数据
2.在一条语句中执行INSERT、UPDATE和DELETE
这种“分块”的方法也能用于INSERTB、UPDATE。对于INSERT和UPDATE,TOP子句就跟在INSERT和UPDATE 关键字后面,例如;
INSERT TOP(100)
UPDATE TOP(25)
可以不用创建多条数据修改语句,而只使用一个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