無意中看到朋友寫的一篇文章“將表里的數據批量生成INSERT語句的存儲過程的實現”。我仔細看文中的兩個存儲代碼，自我感覺兩個都不太滿意，都是生成的單行模式的插入，數據行稍微大些性能會受影響的。所在公司本來就存在第二個版本的類似實現，但是是基于多行模式的，還是需要手工添加UNAION ALL來滿足多行模式的插入。看到這篇博文和基于公司數據行批量腳本的存儲的缺點，這次改寫和增強該存儲的功能。
本存儲運行于SQL Server 2005或以上版本，T-SQL代碼如下：
IF OBJECT_ID(N'dbo.usp_GetInsertSQL', 'P') IS NOT NULL BEGIN DROP PROCEDURE dbo.usp_GetInsertSQL; END GO --================================== -- 功能: 獲取數據表記錄插入的SQL腳本 -- 說明: 具體實現闡述 -- 作者: XXX -- 創建: yyyy-MM-dd -- 修改: yyyy-MM-dd XXX 修改內容描述 --================================== CREATE PROCEDURE dbo.usp_GetInsertSQL ( @chvnTable NVARCHAR(), -- 數據表名稱(建議只使用表名稱，不要帶有分隔符[]) @chvnWhere NVARCHAR() = N'', -- where查詢條件(不帶WHERE關鍵字） @bitIsSingleRow BIT = -- 是否單行模式，默認為單行模式(單行模式為單行INSERT INTO VALUES格式；非單行模式（多行模式）為多行INSERT INTO SELECT格式) ) --$Encode$-- AS BEGIN SET NOCOUNT ON; SET @bitIsSingleRow = ISNULL(@bitIsSingleRow, ); DECLARE @intTableID AS INT, @chvnSchemaTableName NVARCHAR();/*格式：[schema].[table]--++++++（各部分對應字符數）*/ SELECT @intTableID = , @chvnSchemaTableName = N''; SELECT @intTableID = object_id ,@chvnSchemaTableName = QUOTENAME(SCHEMA_NAME(schema_id)) + N'.' + QUOTENAME(@chvnTable) /*組合架構名稱和表名稱的連接*/ FROM sys.objects WHERE name = @chvnTable AND type = 'U'; DECLARE @chvnColumnNames NVARCHAR(), -- 字段列名集，多個以逗號','分隔，格式如：[column_name],[column_name],... @chvnColumnValues AS NVARCHAR(MAX); -- 字段列值集，多個以逗號','分隔 DECLARE @chvnTSQL AS NVARCHAR(MAX), -- TSQL腳本變量 @chvnInsertIntoBoday AS NVARCHAR(); -- InsertInto主體變量 SELECT @chvnTSQL = N'', @chvnInsertIntoBoday = N''; SELECT @chvnColumnNames = ISNULL(@chvnColumnNames + N',', N'') + QUOTENAME(T.column_name) ,@chvnColumnValues = ISNULL(@chvnColumnValues + N' + '','' + ', N'') + CAST(T.column_value AS NVARCHAR()) FROM (SELECT name AS column_name /*字段列名*/ /*字段列值*/ ,column_value = CASE WHEN system_type_id IN (, , , , , , , , , , ) /*數字數據類型：整數數據類型(bit、tinyint、smallint、int、bigint)，帶精度和小數的數據類型(decimal、numeric)和貨幣數據類型（monery和smallmoney*/ THEN 'CASE WHEN '+ name + ' IS NULL THEN ''NULL'' ELSE CAST(' + name + ' AS VARCHAR) END' WHEN system_type_id IN (, , , , ) /*日期和時間數據類型：datetime、smalldatetime（兼容sql server 新增 date、datetime和time）*/ THEN 'CASE WHEN '+ name + ' IS NULL THEN ''NULL'' ELSE '''''''' + REPLACE(CONVERT(VARCHAR(), ' + name + ', ), '' ::.'', '''') + '''''''' END' WHEN system_type_id IN () /*字符串數據類型：varchar*/ THEN 'CASE WHEN '+ name + ' IS NULL THEN ''NULL'' ELSE '''''''' + REPLACE(' + name + ', '''''''', '''''''''''') + '''''''' END' WHEN system_type_id IN () /*Unicode字符串數據類型：nvarchar*/ THEN 'CASE WHEN '+ name + ' IS NULL THEN ''NULL'' ELSE ''N'''''' + REPLACE(' + name + ', '''''''','''''''''''') + '''''''' END' WHEN system_type_id IN () /*字符串數據類型：char*/ 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 () /*nicode字符串數據類型：nchar*/ 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 = @intTableID ) AS T; SET @chvnInsertIntoBoday = N'''INSERT INTO '+ @chvnSchemaTableName + N' (' + @chvnColumnNames + N')'''; -- 方式一、代碼格式使用了GOTO和Label --BEGIN -- IF @bitIsSingleRow = /*多行模式*/ -- BEGIN -- SET @chvnTSQL = N'SELECT ''SELECT '' + ' + @chvnColumnValues + ' AS RowData, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RowNum FROM ' + @chvnSchemaTableName -- -- 此處不能使用GOTO WhereCondition;,因為之后的代碼不會被執行 -- IF @chvnWhere > '' -- BEGIN -- SET @chvnTSQL = @chvnTSQL + ' WHERE ' + @chvnWhere; -- END -- -- 處理多行模式，需要使用ROW_NUMBER窗口函數 -- SET @chvnTSQL = N'SELECT CASE WHEN T.rownum = THEN REPLICATE(N'' '', LEN(N''UNION ALL '') + ) + T.RowData ELSE N''UNION ALL '' + T.RowData END' + -- N' FROM (' + @chvnTSQL + N') AS T'; -- SET @chvnTSQL = N'SELECT '+ @chvnInsertIntoBoday + N';' + -- @chvnTSQL; -- GOTO MultiRow; -- END -- ELSE IF @bitIsSingleRow = /*當行模式*/ -- BEGIN -- SET @chvnTSQL = N'SELECT ' + @chvnInsertIntoBoday + -- N' + ''VALUES('' + ' + @chvnColumnValues + ' + '');'' FROM ' + @chvnSchemaTableName; -- GOTO WhereCondition; -- END -- -- where查詢條件 -- WhereCondition: -- IF @chvnWhere > '' -- BEGIN -- SET @chvnTSQL = @chvnTSQL + ' WHERE ' + @chvnWhere; -- END -- MultiRow:/*多行模式GOTO的Label空標記*/ --END -- 方式二、存在部分代碼的冗余 BEGIN IF @bitIsSingleRow = /*多行模式*/ BEGIN SET @chvnTSQL = N'SELECT ''SELECT '' + ' + @chvnColumnValues + ' AS RowData, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RowNum FROM ' + @chvnSchemaTableName IF @chvnWhere > '' BEGIN SET @chvnTSQL = @chvnTSQL + ' WHERE ' + @chvnWhere; END -- 多行模式特殊代碼，需要使用ROW_NUMBER窗口函數 SET @chvnTSQL = N'SELECT CASE WHEN T.rownum = THEN REPLICATE(N'' '', LEN(N''UNION ALL '') + ) + T.RowData ELSE N''UNION ALL '' + T.RowData END' + N' FROM (' + @chvnTSQL + N') AS T'; SET @chvnTSQL = N'SELECT '+ @chvnInsertIntoBoday + N';' + @chvnTSQL; END ELSE IF @bitIsSingleRow = /*單行模式*/ BEGIN SET @chvnTSQL = N'SELECT ' + @chvnInsertIntoBoday + N' + ''VALUES('' + ' + @chvnColumnValues + ' + '');'' FROM ' + @chvnSchemaTableName; IF @chvnWhere > '' BEGIN SET @chvnTSQL = @chvnTSQL + ' WHERE ' + @chvnWhere; END END END PRINT @chvnTSQL; EXEC(@chvnTSQL); END GO 為了測試以上存儲的效果，下面準備一個有數據的數據表，T-SQL代碼如下：
IF OBJECT_ID(N'dbo.UserLoginInfo', N'U') IS NOT NULL BEGIN DROP TABLE dbo.UserLoginInfo; END GO -- create testing table UserLoginInfo CREATE TABLE dbo.UserLoginInfo ( ID INT IDENTITY(, ) PRIMARY KEY, Name VARCHAR() NOT NULL, LoginTime DATETIME NOT NULL ); GO -- insert testing data INSERT dbo.UserLoginInfo (Name, LoginTime) VALUES ('zhang', '-- ::') ,('li', '-- ::') ,('wang', '-- ::') ,('zhang', '-- ::') ,('li', '-- ::') ,('wang', '-- ::') ,('zhang', '-- ::') ,('li', '-- ::') ,('wang', '-- ::') ,('zhang', '-- ::') ,('li', '-- ::') ,('wang', '-- ::') ,('zhang', '-- ::') ,('li', '-- ::') ,('li', '-- ::') ,('li', '-- ::') ,('li', '-- ::') ,('li', '-- ::') ,('li', '-- ::') ,('li', '-- ::') ,('li', '-- ::') ,('wang', '-- ::') ,('zhang', '-- ::') ,('li', '-- ::') ,('wang', '-- ::') ,('zhang', '-- ::') ,('li', '-- ::') ,('wang', '-- ::') ,('zhang', '-- ::') ,('li', '-- ::') ,('wang', '-- ::'); GO 先測試單行模式的效果，相應的T-SQL代碼如下： EXEC dbo.usp_GetInsertSQL @chvnTable = N'UserLoginInfo', -- nvarchar() @chvnWhere = N'', -- nvarchar() @bitIsSingleRow = ; -- bit GO 執行后的查詢結果如下：
再測試多行模式的效果，相應的T-SQL代碼如下：
EXEC dbo.usp_GetInsertSQL @chvnTable = N'UserLoginInfo', -- nvarchar() @chvnWhere = N'', -- nvarchar() @bitIsSingleRow = ; -- bit GO
執行后的查詢效果如下：
注意：多行模式，還需要將以上的兩個結果前后合并在一個文件就可以啦。
以上內容是小編給大家分享的SQL Server中數據行批量插入腳本的存儲實現，希望大家喜歡。
