SQL Server 原生工具無法根據查詢條件導出 INSERT 腳本,第三方工具收費且靈活性有限。網上常見腳本多為全表導出,大表導出性能差,且缺乏 Schema 支援、數據類型處理不完善。
適用版本:SQL Server 2005+
核心功能:
WHERE 條件篩選導出數據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
EXEC InsertGenerator 'customer', NULL;
-- 或
EXEC InsertGenerator 'customer', '';
EXEC InsertGenerator 'customer', 'city = 3';
EXEC InsertGenerator 'customer', 'city = 3 AND region = 8';
-- 創建測試表
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'))
text/ntext/image 類型截斷風險,建議遷移至 VARCHAR(MAX)/VARBINARY(MAX)SET IDENTITY_INSERT ON另一優秀社區版本,支援 TOP N 限制行數、更完善的類型處理(含 Unicode 轉義)、NULL 關鍵字輸出而非空字符串:
-- 核心優勢:NULL 關鍵字輸出、支援 TOP、更豐富類型映射
EXEC spGenInsertSQL 'customer', 100, 'city = 3 AND didian = ''大連'''; -- 導出前 100 行
EXEC spGenInsertSQL 'customer', NULL, NULL; -- 全表導出
增強版 InsertGenerator 解決了條件導出、多 Schema、類型轉換等實際痛點,適合中小型表數據遷移、測試數據構建、跨環境同步等場景。大表導出建議配合 BCP 或 SSIS 等專業工具。