无意中看到朋友写的一篇文章“将表里的数据批量生成INSERT语句的存储过程的实现”。我仔细看文中的两个存储代码，自我感觉两个都不太满意，都是生成的单行模式的插入，数据行稍微大些性能会受影响的。所在公司本来就存在第二个版本的类似实现，但是是基于多行模式的，还是需要手工添加UNAION ALL来满足多行模式的插入。看到这篇博文和基于公司数据行批量脚本的存储的缺点，这次改写和增强该存储的功能。
本存储运行于SQL Server 2005或以上版本，T-SQL代码如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
|
IF OBJECT_ID(N'dbo.usp_GetInsertSQL', 'P') IS NOT NULLBEGIN DROP PROCEDURE dbo.usp_GetInsertSQL;ENDGO--==================================-- 功能: 获取数据表记录插入的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$--ASBEGIN 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);ENDGO |
为了测试以上存储的效果，下面准备一个有数据的数据表，T-SQL代码如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
|
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代码如下：
|
1
2
3
4
5
|
EXEC dbo.usp_GetInsertSQL @chvnTable = N'UserLoginInfo', -- nvarchar() @chvnWhere = N'', -- nvarchar() @bitIsSingleRow = ; -- bitGO |
执行后的查询效果如下：
注意：多行模式，还需要将以上的两个结果前后合并在一个文件就可以啦。
以上内容是小编给大家分享的SQL Server中数据行批量插入脚本的存储实现，希望大家喜欢。
