SQL数据插入、删除大数据.pdfVIP

SQL数数据据插插⼊⼊、、删删除除⼤⼤数数据据 --测试表 CREATE TA BLE [dbo] .[Employee] ( [EmployeeNo] INT PRIMA RY KEY , [EmployeeName] [nva cha ](50) NULL, [C eateUse ] [nva cha ](50) NULL, [C eateDatetime] [datetime] NULL ); --1、循环插⼊ SET STATISTICS TIME ON; DECLA RE @Index INT = 1; DECLA RE @Time DATETIME = GETDATE(); WHILE @Index = 100000 BEGIN INSERT [dbo] .[Employee](EmployeeNo, EmployeeName, C eateUse , C eateDatetime) VA LUES(@Index , Employee_ + CAST(@Index AS CHA R(6)), system, GETDATE()); SET @Index = @Index + 1; END SELECT DATEDIFF(MS, @Time , GETDATE()) AS [执⾏时间(毫秒)] ; SET STATISTICS TIME OFF; --2、事务循环 BEGIN TRAN; SET STATISTICS TIME ON; DECLA RE @Index INT = 1; DECLA RE @Time DATETIME = GETDATE(); WHILE @Index = 100000 BEGIN INSERT [dbo] .[Employee](EmployeeNo, EmployeeName, C eateUse , C eateDatetime) VA LUES(@Index , Employee_ + CAST(@Index AS CHA R(6)), system, GETDATE()); SET @Index = @Index + 1; END SELECT DATEDIFF(MS, @Time , GETDATE()) AS [执⾏时间(毫秒)] ; SET STATISTICS TIME OFF; COMMIT ; --3、批量插⼊ SET STATISTICS TIME ON; DECLA RE @Time DATETIME = GETDATE(); INSERT [dbo] .[Employee](EmployeeNo, EmployeeName, C eateUse , C eateDatetime) SELECT TOP(100000) EmployeeNo = ROW_NUMBER() OVER (ORDER BY C1.[OBJ ECT_ ID]), Employee_, system, GETDATE() FROM SYS.COLUMNS AS C1 CROSS JOIN SYS.COLUMNS AS C2 ORDER BY C1.[OBJ ECT_ ID] SELECT DATEDIFF(MS, @Time , GETDATE()) AS [执⾏时间(毫秒)] ; SET STATISTICS TIME OFF; --4、CET插⼊ SET STATISTICS TIME ON; DECLA RE @Time DATETIME = GETDATE(); ;WITH CTE(EmployeeNo, EmployeeName, C eateUse , C eateDatetime) AS( SELECT TOP(100000) EmployeeNo = ROW_NUMBER() OVER (ORDER BY C1.[OBJ ECT_ ID]), Employee_, system, GETDATE() FROM SYS.COLUMNS AS C1 CROSS JOIN SYS.COLUMNS AS C2 ORDER BY C1.[OBJ ECT_ ID] ) INSERT [dbo] .[Employee] SELECT EmployeeNo, EmployeeName,