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
执行后的查询效果如下：
注意：多行模式，还需要将以上的两个结果前后合并在一个文件就可以啦。
博友如有其他更好的解决方案，也请不吝赐教，万分感谢。
SQL Server中中数据行批量插入脚本的存储实现
标签：
