萬盛學電腦網

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

SQL Server 批量生成 INSERT 腳本存儲過程增強版:支援條件導出、多 Schema、多數據類型

痛點背景

SQL Server 原生工具無法根據查詢條件導出 INSERT 腳本,第三方工具收費且靈活性有限。網上常見腳本多為全表導出,大表導出性能差,且缺乏 Schema 支援、數據類型處理不完善。

增強版存儲過程:InsertGenerator

適用版本:SQL Server 2005+

核心功能:

  • 支援 WHERE 條件篩選導出數據
  • 自動識別多 Schema 同名表(默認取第一個)
  • 完善處理各類數據類型(字符串、數值、日期、二進制、貨幣等)
  • 自動生成 CONVERT 函數保證類型安全

完整存儲過程代碼

CREATE PROCEDURE InsertGenerator
    @tableName NVARCHAR(MAX),
    @whereClause NVARCHAR(MAX) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @string NVARCHAR(MAX),       -- INSERT INTO 子句
            @stringData NVARCHAR(MAX),    -- VALUES 數據子句
            @dataType NVARCHAR(MAX),
            @schemaName NVARCHAR(MAX),
            @schemaNameCount INT,
            @colName NVARCHAR(MAX);

    -- 處理多 Schema 同名表:統計數量並逐個處理
    SELECT @schemaNameCount = COUNT(*)
    FROM sys.tables t
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    WHERE t.name = @tableName;

    WHILE (@schemaNameCount > 0)
    BEGIN
        -- 獲取當前 Schema 名稱
        SELECT @schemaName = name FROM (
            SELECT ROW_NUMBER() OVER (ORDER BY s.schema_id) AS RowID, s.name
            FROM sys.tables t
            INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
            WHERE t.name = @tableName
        ) AS v WHERE RowID = @schemaNameCount;

        -- 獲取列信息游標
        DECLARE cursCol CURSOR FAST_FORWARD FOR
            SELECT column_name, data_type
            FROM information_schema.columns
            WHERE table_name = @tableName AND table_schema = @schemaName;

        OPEN cursCol;
        SET @string = 'INSERT INTO [' + @schemaName + '].[' + @tableName + '](';
        SET @stringData = '';

        FETCH NEXT FROM cursCol INTO @colName, @dataType;

        IF @@FETCH_STATUS <> 0
        BEGIN
            PRINT 'Table ' + @tableName + ' not found, processing skipped.';
            CLOSE cursCol; DEALLOCATE cursCol;
            SET @schemaNameCount = @schemaNameCount - 1;
            CONTINUE;
        END

        -- 遍歷列構建 SQL 片段
        WHILE @@FETCH_STATUS = 0
        BEGIN
            -- 根據數據類型構建 VALUES 表達式
            IF @dataType IN ('varchar', 'char', 'nchar', 'nvarchar')
                SET @stringData = @stringData + '''''''' + ' + ISNULL(' + @colName + ', '''') + '''''', '' + ';
            ELSE IF @dataType IN ('text', 'ntext')
                SET @stringData = @stringData + '''''''' + ' + ISNULL(CAST(' + @colName + ' AS NVARCHAR(MAX)), '''') + '''''', '' + ';
            ELSE IF @dataType = 'money'
                SET @stringData = @stringData + '''CONVERT(MONEY, '''''' + ISNULL(CAST(' + @colName + ' AS NVARCHAR(MAX)), ''0.00'') + ''''''), '' + ';
            ELSE IF @dataType = 'datetime'
                SET @stringData = @stringData + '''CONVERT(DATETIME, '''''' + ISNULL(CAST(' + @colName + ' AS NVARCHAR(MAX)), '''') + ''''''), '' + ';
            ELSE IF @dataType = 'image'
                SET @stringData = @stringData + '''''''' + ' + ISNULL(CAST(CONVERT(VARBINARY, ' + @colName + ') AS VARCHAR(MAX)), '''') + '''''', '' + ';
            ELSE  -- int, bit, numeric, decimal 等
                SET @stringData = @stringData + '''''''' + ' + ISNULL(CAST(' + @colName + ' AS NVARCHAR(MAX)), '''') + '''''', '' + ';

            SET @string = @string + '[' + @colName + '],';
            FETCH NEXT FROM cursCol INTO @colName, @dataType;
        END

        CLOSE cursCol; DEALLOCATE cursCol;

        -- 構建最終查詢
        DECLARE @query NVARCHAR(MAX);
        IF (@whereClause IS NOT NULL AND @whereClause <> '')
            SET @query = 'SELECT ''' + SUBSTRING(@string, 1, LEN(@string) - 1) + ') VALUES('' + ' + 
                         SUBSTRING(@stringData, 1, LEN(@stringData) - 1) + ''')'' FROM ' + @schemaName + '.' + @tableName + ' WHERE ' + @whereClause;
        ELSE
            SET @query = 'SELECT ''' + SUBSTRING(@string, 1, LEN(@string) - 1) + ') VALUES('' + ' + 
                         SUBSTRING(@stringData, 1, LEN(@stringData) - 1) + ''')'' FROM ' + @schemaName + '.' + @tableName;

        PRINT @query;
        EXEC sp_executesql @query;

        SET @schemaNameCount = @schemaNameCount - 1;
    END
END
GO

使用示例

1. 導出全表數據

EXEC InsertGenerator 'customer', NULL;
-- 或
EXEC InsertGenerator 'customer', '';

2. 條件導出

EXEC InsertGenerator 'customer', 'city = 3';
EXEC InsertGenerator 'customer', 'city = 3 AND region = 8';

3. 測試表驗證

-- 創建測試表
CREATE TABLE testinsert (id INT, name VARCHAR(100), cash MONEY, dtime DATETIME);
INSERT INTO testinsert VALUES (1, 'nihao', 8.8, GETDATE());

-- 測試導出
EXEC InsertGenerator 'testinsert', '';
EXEC InsertGenerator 'testinsert', 'name = ''nihao''';
EXEC InsertGenerator 'testinsert', 'name = ''nihao'' AND cash = 8.8';

生成結果示例

INSERT INTO [dbo].[testinsert]([id],[name],[cash],[dtime]) 
VALUES('1','nihao',CONVERT(MONEY,'8.80'),CONVERT(DATETIME,'2015-08-02 17:17:00'))

已知限制與改進方向

  • 多 Schema 同名表:當前僅導出第一個 Schema,可擴展參數指定 Schema
  • 數值類型輸出帶引號:所有值均作為字符串輸出,依賴隱式轉換或 CONVERT,嚴格模式下建議按類型輸出無引號數值
  • 大文本/二進制:text/ntext/image 類型截斷風險,建議遷移至 VARCHAR(MAX)/VARBINARY(MAX)
  • Identity 列:導入目標表需開啟 SET IDENTITY_INSERT ON

社區共享替代方案:spGenInsertSQL

另一優秀社區版本,支援 TOP N 限制行數、更完善的類型處理(含 Unicode 轉義)、NULL 關鍵字輸出而非空字符串:

-- 核心優勢:NULL 關鍵字輸出、支援 TOP、更豐富類型映射
EXEC spGenInsertSQL 'customer', 100, 'city = 3 AND didian = ''大連''';  -- 導出前 100 行
EXEC spGenInsertSQL 'customer', NULL, NULL;  -- 全表導出

總結

增強版 InsertGenerator 解決了條件導出、多 Schema、類型轉換等實際痛點,適合中小型表數據遷移、測試數據構建、跨環境同步等場景。大表導出建議配合 BCP 或 SSIS 等專業工具。

copyright © 萬盛學電腦網 all rights reserved