萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 批量 INSERT 腳本生成存儲過程:單行/多行模式雙模優化版

背景與改進動機

針對現有單行 INSERT INTO ... VALUES 模式在大數據量下性能差的問題,本存儲過程實現雙模式輸出:

  • 單行模式:傳統 INSERT INTO ... VALUES (...) 逐行生成,適合小數據量
  • 多行模式:INSERT INTO ... SELECT ... UNION ALL SELECT ... 批量生成,大幅提升執行效率

存儲過程:usp_GetInsertSQL

IF OBJECT_ID(N'dbo.usp_GetInsertSQL', 'P') IS NOT NULL
    DROP PROCEDURE dbo.usp_GetInsertSQL;
GO

CREATE PROCEDURE dbo.usp_GetInsertSQL
    @chvnTable NVARCHAR(128),          -- 表名(不含方括號)
    @chvnWhere NVARCHAR(MAX) = N'',    -- WHERE 條件(不含 WHERE 關鍵字)
    @bitIsSingleRow BIT = 1            -- 1=單行模式,0=多行模式
AS
BEGIN
    SET NOCOUNT ON;
    SET @bitIsSingleRow = ISNULL(@bitIsSingleRow, 1);

    DECLARE @intTableID INT,
            @chvnSchemaTableName NVARCHAR(260),
            @chvnColumnNames NVARCHAR(MAX),
            @chvnColumnValues NVARCHAR(MAX),
            @chvnTSQL NVARCHAR(MAX),
            @chvnInsertIntoBody NVARCHAR(MAX);

    -- 獲取表對象 ID 與 Schema 限定名
    SELECT @intTableID = object_id,
           @chvnSchemaTableName = QUOTENAME(SCHEMA_NAME(schema_id)) + N'.' + QUOTENAME(@chvnTable)
    FROM sys.objects
    WHERE name = @chvnTable AND type = 'U';

    IF @intTableID IS NULL
    BEGIN
        RAISERROR('Table %s not found.', 16, 1, @chvnTable);
        RETURN;
    END

    -- 構建列名列表與值表達式(按類型處理)
    SELECT 
        @chvnColumnNames = ISNULL(@chvnColumnNames + N',', N'') + QUOTENAME(T.column_name),
        @chvnColumnValues = ISNULL(@chvnColumnValues + N' + '','' + ', N'') + CAST(T.column_value AS NVARCHAR(MAX))
    FROM (
        SELECT 
            name AS column_name,
            column_value = CASE
                WHEN system_type_id IN (48,52,56,59,60,62,104,106,108,122,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(23), ' + name + ', 121), ''T'', '' '') + '''''''' END'
                WHEN system_type_id IN (167, 175)  -- varchar, char
                    THEN 'CASE WHEN ' + name + ' IS NULL THEN ''NULL'' ELSE '''''''' + REPLACE(' + name + ', '''''''', '''''''''''') + '''''''' END'
                WHEN system_type_id IN (231, 239)  -- nvarchar, nchar
                    THEN 'CASE WHEN ' + name + ' IS NULL THEN ''NULL'' ELSE ''N'''''' + REPLACE(' + name + ', '''''''', '''''''''''') + '''''''' END'
                ELSE '''NULL'''
            END
        FROM sys.columns
        WHERE object_id = @intTableID
    ) AS T;

    SET @chvnInsertIntoBody = N'''INSERT INTO ' + @chvnSchemaTableName + N' (' + @chvnColumnNames + N')''';

    -- 雙模式邏輯
    IF @bitIsSingleRow = 0  -- 多行模式
    BEGIN
        SET @chvnTSQL = N'SELECT ''SELECT '' + ' + @chvnColumnValues + ' AS RowData, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RowNum FROM ' + @chvnSchemaTableName;
        IF @chvnWhere > '' SET @chvnTSQL = @chvnTSQL + ' WHERE ' + @chvnWhere;

        -- 使用 ROW_NUMBER 構建 UNION ALL 鏈
        SET @chvnTSQL = N'SELECT CASE WHEN T.rownum = 1 THEN REPLICATE(N'' '', LEN(N''UNION ALL '') + 1) + T.RowData ELSE N''UNION ALL '' + T.RowData END' +
                       N' FROM (' + @chvnTSQL + N') AS T';
        SET @chvnTSQL = N'SELECT ' + @chvnInsertIntoBody + N';' + @chvnTSQL;
    END
    ELSE  -- 單行模式
    BEGIN
        SET @chvnTSQL = N'SELECT ' + @chvnInsertIntoBody + N' + ''VALUES('' + ' + @chvnColumnValues + N' + '');'' FROM ' + @chvnSchemaTableName;
        IF @chvnWhere > '' SET @chvnTSQL = @chvnTSQL + ' WHERE ' + @chvnWhere;
    END

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

測試驗證

準備測試數據

IF OBJECT_ID(N'dbo.UserLoginInfo', N'U') IS NOT NULL DROP TABLE dbo.UserLoginInfo;
GO

CREATE TABLE dbo.UserLoginInfo (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    LoginTime DATETIME NOT NULL
);
GO

INSERT dbo.UserLoginInfo (Name, LoginTime) VALUES
('zhang', '2015-12-24 14:52:13'), ('li', '2015-12-24 14:52:13'), ('wang', '2015-12-24 14:52:13'),
('zhang', '2015-12-24 14:52:13'), ('li', '2015-12-24 14:52:13'), ('wang', '2015-12-24 14:52:13');
-- 更多測試數據...
GO

單行模式測試

EXEC dbo.usp_GetInsertSQL 
    @chvnTable = N'UserLoginInfo',
    @chvnWhere = N'',
    @bitIsSingleRow = 1;
GO

輸出格式:

INSERT INTO [dbo].[UserLoginInfo] ([ID],[Name],[LoginTime]) VALUES('1','zhang','2015-12-24 14:52:13.000');
INSERT INTO [dbo].[UserLoginInfo] ([ID],[Name],[LoginTime]) VALUES('2','li','2015-12-24 14:52:13.000');
...

多行模式測試

EXEC dbo.usp_GetInsertSQL 
    @chvnTable = N'UserLoginInfo',
    @chvnWhere = N'',
    @bitIsSingleRow = 0;
GO

輸出格式:

INSERT INTO [dbo].[UserLoginInfo] ([ID],[Name],[LoginTime]);
SELECT '1','zhang','2015-12-24 14:52:13.000'
UNION ALL SELECT '2','li','2015-12-24 14:52:13.000'
UNION ALL SELECT '3','wang','2015-12-24 14:52:13.000'
...

注意:多行模式需手動將兩部分結果合併為單個腳本文件執行。

核心優化點解析

  1. 類型安全轉換:按 system_type_id 分類處理數值、日期、字符串、Unicode,自動處理 NULL、單引號轉義、日期格式標準化(121 標準格式)
  2. 多行模式性能優勢:單條語句批量插入,減少網絡往返、編譯開銷、事務日誌寫入次數
  3. 動態 SQL 防注入:列名通過 QUOTENAME 防注入,值通過參數化表達式構建
  4. Schema 感知:自動解析 Schema 限定名,支援非 dbo 架構

適用場景與建議

場景推薦模式說明
數據遷移(< 1 萬行)多行模式單條語句執行,速度提升 10-50 倍
大數據量遷移(> 10 萬行)BCP / SSIS存儲過程受限於內存與日誌,專業工具更適合
測試數據構建單行模式便於閱讀、調試、部分插入
跨版本兼容腳本單行模式多行模式需 SQL Server 2005+ 支援 ROW_NUMBER

總結

雙模式存儲過程 usp_GetInsertSQL 靈活應對不同規模導出需求,多行模式通過 UNION ALL 批量插入顯著提升大數據量場景性能,類型處理邏輯完善覆蓋主流類型,是 DBA 與開發人員工具箱中的實用組件。

copyright © 萬盛學電腦網 all rights reserved