优化SQLServer批量数据插入存储过程的实现

IF OBJECT_ID('dbo.usp_GenerateInsertScript', 'P') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.usp_GenerateInsertScript;
END
GO
--==================================
-- 功能: 生成数据表记录插入的SQL脚本
-- 说明: 支持单行和多行模式
-- 作者: xxx
-- 创建: yyyy-mm-dd
-- 修改: yyyy-mm-dd 描述修改内容
--==================================
CREATE PROCEDURE dbo.usp_GenerateInsertScript
(
    @TableName NVARCHAR(128), -- 数据表名称
    @WhereClause NVARCHAR(MAX) = N'', -- WHERE查询条件
    @IsSingleRow BIT = 1 -- 是否为单行模式，默认为单行模式
)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE
        @TableID INT,
        @SchemaTableName NVARCHAR(256),
        @ColumnNames NVARCHAR(MAX),
        @ColumnValues NVARCHAR(MAX),
        @SQL NVARCHAR(MAX),
        @InsertIntoPart NVARCHAR(MAX);

    SELECT
        @TableID = object_id,
        @SchemaTableName = QUOTENAME(schema_name(schema_id)) + '.' + QUOTENAME(@TableName)
    FROM sys.objects
    WHERE name = @TableName AND type = 'U';

    SELECT
        @ColumnNames = ISNULL(@ColumnNames + ', ', '') + QUOTENAME(name),
        @ColumnValues = ISNULL(@ColumnValues + ' + '', '' + ', '') + CASE
            WHEN system_type_id IN (48, 52, 56, 59, 60, 62, 106, 108, 127) THEN 'CASE WHEN ' + name + ' IS NULL THEN ''NULL'' ELSE CAST(' + name + ' AS VARCHAR) END'
            WHEN system_type_id IN (40, 41, 42, 43, 58, 61) THEN 'CASE WHEN ' + name + ' IS NULL THEN ''NULL'' ELSE '''''''' + REPLACE(CONVERT(VARCHAR, ' + name + '), '' ::.'', '''') + '''''''' END'
            WHEN system_type_id IN (167) THEN 'CASE WHEN ' + name + ' IS NULL THEN ''NULL'' ELSE '''''''' + REPLACE(' + name + ', '''''''', '''''''''''') + '''''''' END'
            WHEN system_type_id IN (231) THEN 'CASE WHEN ' + name + ' IS NULL THEN ''NULL'' ELSE ''N'''''' + REPLACE(' + name + ', '''''''','''''''''''') + '''''''' END'
            WHEN system_type_id IN (175) THEN 'CASE WHEN ' + name + ' IS NULL THEN ''NULL'' ELSE '''''''' + CAST(REPLACE(' + name + ', '''''''' ,'''''''''''') AS CHAR(' + CAST(max_length AS VARCHAR) + ')) + '''''''' END'
            WHEN system_type_id IN (239) THEN 'CASE WHEN ' + name + ' IS NULL THEN ''NULL'' ELSE ''N'''''' + CAST(REPLACE(' + name + ', '''''''' ,'''''''''''') AS CHAR(' + CAST(max_length AS VARCHAR) + ')) + '''''''' END'
            ELSE '''NULL'''
        END
    FROM sys.columns
    WHERE object_id = @TableID;

    SET @InsertIntoPart = N'INSERT INTO ' + @SchemaTableName + N' (' + @ColumnNames + N')';

    IF @IsSingleRow = 1
    BEGIN
        SET @SQL = N'SELECT ' + @InsertIntoPart + N' + '' VALUES ('' + ' + @ColumnValues + N' + '')'' FROM ' + @SchemaTableName;
    END
    ELSE
    BEGIN
        SET @SQL = N'SELECT ''SELECT '' + ' + @ColumnValues + N' AS RowData, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum FROM ' + @SchemaTableName;
        IF LEN(@WhereClause) > 0
        BEGIN
            SET @SQL = @SQL + N' WHERE ' + @WhereClause;
        END
        SET @SQL = N'SELECT CASE WHEN t.RowNum = 1 THEN REPLICATE(N'' '', LEN(N''UNION ALL '') + 1) + t.RowData ELSE N''UNION ALL '' + t.RowData END FROM (' + @SQL + N') AS t';
        SET @SQL = N'SELECT ' + @InsertIntoPart + N';' + @SQL;
    END

    PRINT @SQL;
    EXEC (@SQL);
END
GO

-- 创建测试表 userlogininfo
IF OBJECT_ID('dbo.userlogininfo', 'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.userlogininfo;
END
GO
CREATE TABLE dbo.userlogininfo (
    id INT IDENTITY(1, 1) PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    logintime DATETIME NOT NULL
);
GO
-- 插入测试数据
INSERT INTO dbo.userlogininfo (name, logintime) VALUES
('zhang', '2023-01-01 08:00:00'),
('li', '2023-01-01 09:00:00'),
('wang', '2023-01-01 10:00:00');
GO

-- 测试单行模式
EXEC dbo.usp_GenerateInsertScript
    @TableName = N'userlogininfo',
    @WhereClause = N'',
    @IsSingleRow = 1;
GO

-- 测试多行模式
EXEC dbo.usp_GenerateInsertScript
    @TableName = N'userlogininfo',
    @WhereClause = N'',
    @IsSingleRow = 0;
GO