SQL Server中数据行批量插入脚本的存储过程实现

这篇文章主要介绍了SQL Server中数据行批量插入脚本的存储实现 的相关资料,需要的朋友可以参考下

无意中看到朋友写的一篇文章“将表里的数据批量生成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 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代码如下:

?

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

执行后的查询结果如下:

SQL Server中数据行批量插入脚本的存储过程实现

再测试多行模式的效果,相应的T-SQL代码如下:

?

1

2

3

4

5

EXEC dbo.usp_GetInsertSQL

@chvnTable = N'UserLoginInfo', — nvarchar()

@chvnWhere = N'', — nvarchar()

@bitIsSingleRow = ; — bit

GO

执行后的查询效果如下:

SQL Server中数据行批量插入脚本的存储过程实现

注意:多行模式,还需要将以上的两个结果前后合并在一个文件就可以啦。

以上内容是小编给大家分享的SQL Server中数据行批量插入脚本的存储实现,希望大家喜欢。

版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/26788/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

(0)
上一篇 2022-09-28 8:21:28
下一篇 2022-09-28 8:21:35

软件定制开发公司

相关阅读

发表回复

登录后才能评论
通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!