SQL 数据插入、删除大数据

女爷i 2022-03-15 02:36 232阅读 0赞

转自：[https://www.cnblogs.com/lenovo\_tiger\_love/p/3719989.html][https_www.cnblogs.com_lenovo_tiger_love_p_3719989.html]

[几种数据库的大数据批量插入][Link 1]: [https://www.cnblogs.com/telwanggs/p/7485378.html][Link 1]

# [SQL 数据插入、删除 大数据][https_www.cnblogs.com_lenovo_tiger_love_p_3719989.html] #

--测试表
    CREATE TABLE [dbo].[Employee] (
        [EmployeeNo] INT PRIMARY KEY,
        [EmployeeName] [nvarchar](50) NULL,
        [CreateUser] [nvarchar](50) NULL,
        [CreateDatetime] [datetime] NULL
    );

--1、循环插入
    SET STATISTICS TIME ON;
    DECLARE @Index INT = 1;
    DECLARE @Timer DATETIME = GETDATE();
    
    WHILE @Index <= 100000
    BEGIN
        INSERT [dbo].[Employee](EmployeeNo, EmployeeName, CreateUser, CreateDatetime) VALUES(@Index, 'Employee_' + CAST(@Index AS CHAR(6)), 'system', GETDATE());
        SET @Index = @Index + 1;
    END
    
    SELECT DATEDIFF(MS, @Timer, GETDATE()) AS [执行时间(毫秒)];
    SET STATISTICS TIME OFF;

--2、事务循环
    BEGIN TRAN;
    SET STATISTICS TIME ON;
    DECLARE @Index INT = 1;
    DECLARE @Timer DATETIME = GETDATE();
    
    WHILE @Index <= 100000
    BEGIN
        INSERT [dbo].[Employee](EmployeeNo, EmployeeName, CreateUser, CreateDatetime) VALUES(@Index, 'Employee_' + CAST(@Index AS CHAR(6)), 'system', GETDATE());
        SET @Index = @Index + 1;
    END
    
    SELECT DATEDIFF(MS, @Timer, GETDATE()) AS [执行时间(毫秒)];
    SET STATISTICS TIME OFF;
    COMMIT;
    --3、批量插入

SET STATISTICS TIME ON;
    DECLARE @Timer DATETIME = GETDATE();
    
    INSERT [dbo].[Employee](EmployeeNo, EmployeeName, CreateUser, CreateDatetime)
    SELECT TOP(100000) EmployeeNo = ROW_NUMBER() OVER (ORDER BY C1.[OBJECT_ID]), 'Employee_', 'system', GETDATE()
    FROM SYS.COLUMNS AS C1 CROSS JOIN SYS.COLUMNS AS C2
    ORDER BY C1.[OBJECT_ID]
    
    SELECT DATEDIFF(MS, @Timer, GETDATE()) AS [执行时间(毫秒)];
    SET STATISTICS TIME OFF;
    --4、CET插入

SET STATISTICS TIME ON;
    DECLARE @Timer DATETIME = GETDATE();
    
    ;WITH CTE(EmployeeNo, EmployeeName, CreateUser, CreateDatetime) AS(
        SELECT TOP(100000) EmployeeNo = ROW_NUMBER() OVER (ORDER BY C1.[OBJECT_ID]), 'Employee_', 'system', GETDATE()
        FROM SYS.COLUMNS AS C1 CROSS JOIN SYS.COLUMNS AS C2
        ORDER BY C1.[OBJECT_ID]
    )
    INSERT [dbo].[Employee] SELECT EmployeeNo, EmployeeName, CreateUser, CreateDatetime FROM CTE;
    
    SELECT DATEDIFF(MS, @Timer, GETDATE()) AS [执行时间(毫秒)];
    SET STATISTICS TIME OFF;
    --5、循环删除

SET STATISTICS TIME ON;
    DECLARE @Timer DATETIME = GETDATE();
    
    DELETE FROM [dbo].[Employee];
    
    SELECT DATEDIFF(MS, @Timer, GETDATE()) AS [执行时间(毫秒)];
    SET STATISTICS TIME OFF;
    --6、批量删除

SET STATISTICS TIME ON;
    DECLARE @Timer DATETIME = GETDATE();
    
    SET ROWCOUNT 100000;
    
    WHILE 1 = 1
    BEGIN
        BEGIN TRAN
        DELETE FROM [dbo].[Employee];
        COMMIT
        IF @@ROWCOUNT = 0
            BREAK;
    END
    
    SET ROWCOUNT 0;
    
    SELECT DATEDIFF(MS, @Timer, GETDATE()) AS [执行时间(毫秒)];
    SET STATISTICS TIME OFF;
    --6、Truncate删除

SET STATISTICS TIME ON;
    DECLARE @Timer DATETIME = GETDATE();
    
    TRUNCATE TABLE [dbo].[Employee];
    
    SELECT DATEDIFF(MS, @Timer, GETDATE()) AS [执行时间(毫秒)];
    SET STATISTICS TIME OFF;

[https_www.cnblogs.com_lenovo_tiger_love_p_3719989.html]: https://www.cnblogs.com/lenovo_tiger_love/p/3719989.html
[Link 1]: https://www.cnblogs.com/telwanggs/p/7485378.html