SQL Server中中数据行批量插入脚本的存储实现
看到博友SQL Server MVP桦仔的一篇博文“将表里的数据批量生成INSERT语句的存储过程的实现”。我仔细看来博文中的两个存储代码，自我感觉两个都不太满意，都是生成的单行模式的插入，数据行稍微大些性能会受影响的。所在公司本来就存在第二个版本的类似实现，但是是基于多行模式的，还是需要手工添加UNAION ALL来满足多行模式的插入。看到这篇博文和基于公司数据行批量脚本的存储的缺点，这次改写和增强该存储的功能。
本存储运行于SQL Server 2005或以上版本，T-SQL代码如下：
1 IF OBJECT_ID(N'dbo.usp_GetInsertSQL', 'P') IS NOT NULL 2 BEGIN 3 DROP PROCEDURE dbo.usp_GetInsertSQL; 4 END 5 GO 6 7 --================================== 8 -- 功能: 获取数据表记录插入的SQL脚本 9 -- 说明: 具体实现阐述 10 -- 作者: XXX 11 -- 创建: yyyy-MM-dd 12 -- 修改: yyyy-MM-dd XXX 修改内容描述 13 --================================== 14 CREATE PROCEDURE dbo.usp_GetInsertSQL 15 ( 16 @chvnTable NVARCHAR(128), -- 数据表名称(建议只使用表名称，不要带有分隔符[]) 17 @chvnWhere NVARCHAR(1000) = N'', -- where查询条件(不带WHERE关键字） 18 @bitIsSingleRow BIT = 1 -- 是否单行模式，默认为单行模式(单行模式为单行INSERT INTO VALUES格式；非单行模式（多行模式）为多行INSERT INTO SELECT格式) 19 ) 20 --$Encode$-- 21 AS 22 BEGIN 23 SET NOCOUNT ON; 24 25 SET @bitIsSingleRow = ISNULL(@bitIsSingleRow, 1); 26 27 DECLARE 28 @intTableID AS INT, 29 @chvnSchemaTableName NVARCHAR(261);/*格式：[schema].[table]--1+128+1+1+1+128+1（各部分对应字符数）*/ 30 31 SELECT 32 @intTableID = 0, 33 @chvnSchemaTableName = N''; 34 35 SELECT 36 @intTableID = object_id 37 ,@chvnSchemaTableName = QUOTENAME(SCHEMA_NAME(schema_id)) + N'.' + QUOTENAME(@chvnTable) /*组合架构名称和表名称的连接*/ 38 FROM sys.objects 39 WHERE name = @chvnTable 40 AND type = 'U'; 41 42 DECLARE 43 @chvnColumnNames NVARCHAR(2000), -- 字段列名集，多个以逗号','分隔，格式如：[column_name],[column_name],... 44 @chvnColumnValues AS NVARCHAR(MAX); -- 字段列值集，多个以逗号','分隔 45 46 DECLARE 47 @chvnTSQL AS NVARCHAR(MAX), -- TSQL脚本变量 48 @chvnInsertIntoBoday AS NVARCHAR(2100); -- InsertInto主体变量 49 SELECT 50 @chvnTSQL = N'', 51 @chvnInsertIntoBoday = N''; 52 53 SELECT 54 @chvnColumnNames = ISNULL(@chvnColumnNames + N',', N'') + QUOTENAME(T.column_name) 55 ,@chvnColumnValues = ISNULL(@chvnColumnValues + N' + '','' + ', N'') + CAST(T.column_value AS NVARCHAR(800)) 56 FROM (SELECT 57 name AS column_name /*字段列名*/ 58 /*字段列值*/ 59 ,column_value = CASE 60 WHEN system_type_id IN (48, 52, 56, 59, 60, 62, 104, 106, 108, 122, 127) /*数字数据类型：整数数据类型(bit、tinyint、smallint、int、bigint)，带精度和小数的数据类型(decimal、numeric)和货币数据类型（monery和smallmoney*/ 61 THEN 'CASE WHEN '+ name + ' IS NULL THEN ''NULL'' ELSE CAST(' + name + ' AS VARCHAR) END' 62 WHEN system_type_id IN (58, 61, 40, 41, 42) /*日期和时间数据类型：datetime、smalldatetime（兼容sql server 2008新增 date、datetime2和time）*/ 63 THEN 'CASE WHEN '+ name + ' IS NULL THEN ''NULL'' ELSE '''''''' + REPLACE(CONVERT(VARCHAR(22), ' + name + ', 23), '' 00:00:00.000'', '''') + '''''''' END' 64 WHEN system_type_id IN (167) /*字符串数据类型：varchar*/ 65 THEN 'CASE WHEN '+ name + ' IS NULL THEN ''NULL'' ELSE '''''''' + REPLACE(' + name + ', '''''''', '''''''''''') + '''''''' END' 66 WHEN system_type_id IN (231) /*Unicode字符串数据类型：nvarchar*/ 67 THEN 'CASE WHEN '+ name + ' IS NULL THEN ''NULL'' ELSE ''N'''''' + REPLACE(' + name + ', '''''''','''''''''''') + '''''''' END' 68 WHEN system_type_id IN (175) /*字符串数据类型：char*/ 69 THEN 'CASE WHEN '+ name + ' IS NULL THEN ''NULL'' ELSE '''''''' + CAST(REPLACE(' + name + ', '''''''' ,'''''''''''') AS CHAR(' + CAST(max_length AS VARCHAR) + ')) + '''''''' END' 70 WHEN system_type_id IN (239) /*nicode字符串数据类型：nchar*/ 71 THEN 'CASE WHEN '+ name + ' IS NULL THEN ''NULL'' ELSE ''N'''''' + CAST(REPLACE(' + name + ', '''''''' ,'''''''''''') AS CHAR(' + CAST(max_length AS VARCHAR) + ')) + '''''''' END' 72 ELSE '''NULL''' END 73 FROM sys.columns 74 WHERE object_id = @intTableID 75 ) AS T; 76 77 SET @chvnInsertIntoBoday = N'''INSERT INTO '+ @chvnSchemaTableName + N' (' + @chvnColumnNames + N')'''; 78 -- 方式一、代码格式使用了GOTO和Label 79 --BEGIN 80 -- IF @bitIsSingleRow = 0 /*多行模式*/ 81 -- BEGIN 82 -- SET @chvnTSQL = N'SELECT ''SELECT '' + ' + @chvnColumnValues + ' AS RowData, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RowNum FROM ' + @chvnSchemaTableName 83 84 85 -- -- 此处不能使用GOTO WhereCondition;,因为之后的代码不会被执行 86 -- IF @chvnWhere > '' 87 -- BEGIN 88 -- SET @chvnTSQL = @chvnTSQL + ' WHERE ' + @chvnWhere; 89 -- END 90 91 -- -- 处理多行模式，需要使用ROW_NUMBER窗口函数 92 -- 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' + 93 -- N' FROM (' + @chvnTSQL + N') AS T'; 94 95 -- SET @chvnTSQL = N'SELECT '+ @chvnInsertIntoBoday + N';' + 96 -- @chvnTSQL; 97 98 -- GOTO MultiRow; 99 -- END 100 -- ELSE IF @bitIsSingleRow = 1 /*当行模式*/ 101 -- BEGIN 102 -- SET @chvnTSQL = N'SELECT ' + @chvnInsertIntoBoday + 103 -- N' + ''VALUES('' + ' + @chvnColumnValues + ' + '');'' FROM ' + @chvnSchemaTableName; 104 105 -- GOTO WhereCondition; 106 -- END 107 108 -- -- where查询条件 109 -- WhereCondition: 110 -- IF @chvnWhere > '' 111 -- BEGIN 112 -- SET @chvnTSQL = @chvnTSQL + ' WHERE ' + @chvnWhere; 113 -- END 114 115 116 -- MultiRow:/*多行模式GOTO的Label空标记*/ 117 118 --END 119 120 -- 方式二、存在部分代码的冗余 121 BEGIN 122 IF @bitIsSingleRow = 0 /*多行模式*/ 123 BEGIN 124 SET @chvnTSQL = N'SELECT ''SELECT '' + ' + @chvnColumnValues + ' AS RowData, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RowNum FROM ' + @chvnSchemaTableName 125 126 IF @chvnWhere > '' 127 BEGIN 128 SET @chvnTSQL = @chvnTSQL + ' WHERE ' + @chvnWhere; 129 END 130 131 -- 多行模式特殊代码，需要使用ROW_NUMBER窗口函数 132 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' + 133 N' FROM (' + @chvnTSQL + N') AS T'; 134 135 SET @chvnTSQL = N'SELECT '+ @chvnInsertIntoBoday + N';' + 136 @chvnTSQL; 137 END 138 ELSE IF @bitIsSingleRow = 1 /*单行模式*/ 139 BEGIN 140 SET @chvnTSQL = N'SELECT ' + @chvnInsertIntoBoday + 141 N' + ''VALUES('' + ' + @chvnColumnValues + ' + '');'' FROM ' + @chvnSchemaTableName; 142 143 IF @chvnWhere > '' 144 BEGIN 145 SET @chvnTSQL = @chvnTSQL + ' WHERE ' + @chvnWhere; 146 END 147 END 148 END 149 150 PRINT @chvnTSQL; 151 EXEC(@chvnTSQL); 152 END 153 GO
为了测试以上存储的效果，下面准备一个有数据的数据表，T-SQL代码如下：
1 IF OBJECT_ID(N'dbo.UserLoginInfo', N'U') IS NOT NULL 2 BEGIN 3 DROP TABLE dbo.UserLoginInfo; 4 END 5 GO 6 7 -- create testing table UserLoginInfo 8 CREATE TABLE dbo.UserLoginInfo ( 9 ID INT IDENTITY(1, 1) PRIMARY KEY, 10 Name VARCHAR(50) NOT NULL, 11 LoginTime DATETIME NOT NULL 12 ); 13 GO 14 15 -- insert testing data 16 INSERT dbo.UserLoginInfo (Name, LoginTime) VALUES 17 ('zhang', '2015-11-10 12:01:50') 18 ,('li', '2015-11-11 11:01:50') 19 ,('wang', '2015-11-9 11:01:50') 20 ,('zhang', '2015-11-11 12:01:50') 21 ,('li', '2015-11-11 12:01:50') 22 ,('wang', '2015-11-11 11:01:50') 23 ,('zhang', '2015-11-12 12:01:50') 24 ,('li', '2015-11-13 13:01:50') 25 ,('wang', '2015-11-12 11:01:50') 26 ,('zhang', '2015-11-13 12:01:50') 27 ,('li', '2015-11-14 11:01:50') 28 ,('wang', '2015-11-14 11:01:50') 29 ,('zhang', '2015-11-10 12:01:50') 30 ,('li', '2013-10-05 11:01:50') 31 ,('li', '2013-10-06 11:01:50') 32 ,('li', '2014-10-05 11:01:50') 33 ,('li', '2014-10-06 11:01:50') 34 ,('li', '2015-10-05 11:01:50') 35 ,('li', '2015-10-06 11:01:50') 36 ,('li', '2015-11-10 11:01:50') 37 ,('li', '2015-11-11 11:01:50') 38 ,('wang', '2015-11-09 11:01:50') 39 ,('zhang', '2015-11-11 12:01:50') 40 ,('li', '2015-11-11 12:01:50') 41 ,('wang', '2015-11-11 11:01:50') 42 ,('zhang', '2015-11-12 12:01:50') 43 ,('li', '2015-11-13 13:01:50') 44 ,('wang', '2015-11-12 11:01:50') 45 ,('zhang', '2015-11-13 12:01:50') 46 ,('li', '2015-11-14 11:01:50') 47 ,('wang', '2015-11-14 11:01:50'); 48 GO
先测试单行模式的效果，相应的T-SQL代码如下：
1 EXEC dbo.usp_GetInsertSQL 2 @chvnTable = N'UserLoginInfo', -- nvarchar(128) 3 @chvnWhere = N'', -- nvarchar(1000) 4 @bitIsSingleRow = 1; -- bit 5 GO
执行后的查询结果如下：
再测试多行模式的效果，相应的T-SQL代码如下：
1 EXEC dbo.usp_GetInsertSQL 2 @chvnTable = N'UserLoginInfo', -- nvarchar(128) 3 @chvnWhere = N'', -- nvarchar(1000) 4 @bitIsSingleRow = 0; -- bit 5 GO
执行后的查询效果如下：
注意：多行模式，还需要将以上的两个结果前后合并在一个文件就可以啦。
博友如有其他更好的解决方案，也请不吝赐教，万分感谢。
