将表里的数据批量生成INSERT语句的存储过程 增强版

这篇文章主要介绍了将表里的数据批量生成INSERT语句的存储过程 增强版的相关资料,需要的朋友可以参考下

有时候,我们需要将某个表里的数据全部或者根据查询条件导出来,迁移到另一个相同结构的库中

目前SQL Server里面是没有相关的工具根据查询条件来生成INSERT语句的,只有借助第三方工具(third party tools)

这种脚本网上也有很多,但是网上的脚本还是欠缺一些规范和功能,例如:我只想导出特定查询条件的数据,网上的脚本都是导出全表数据

如果表很大,对性能会有很大影响

这里有一个存储过程(适用于SQLServer2005 或以上版本)

?

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

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

— Author: <桦仔>

— Blog: <http://www.cnblogs.com/lyhabc/>

— Create date: <//>

— Description: <根据查询条件导出表数据的insert脚本>

— =============================================

CREATE PROCEDURE InsertGenerator

(

@tableName NVARCHAR(MAX),

@whereClause NVARCHAR(MAX)

)

AS

–Then it includes a cursor to fetch column specific information (column name and the data type thereof)

–from information_schema.columns pseudo entity and loop through for building the INSERT and VALUES clauses

–of an INSERT DML statement.

DECLARE @string NVARCHAR(MAX) –for storing the first half of INSERT statement

DECLARE @stringData NVARCHAR(MAX) –for storing the data (VALUES) related statement

DECLARE @dataType NVARCHAR(MAX) –data types returned for respective columns

DECLARE @schemaName NVARCHAR(MAX) –schema name returned from sys.schemas

DECLARE @schemaNameCount int–shema count

DECLARE @QueryString NVARCHAR(MAX) — provide for the whole query,

set @QueryString=' '

–如果有多个schema,选择其中一个schema

SELECT @schemaNameCount=COUNT(*)

FROM sys.tables t

INNER JOIN sys.schemas s ON t.schema_id = s.schema_id

WHERE t.name = @tableName

WHILE(@schemaNameCount>)

BEGIN

–如果有多个schema,依次指定

select @schemaName = name

from

(

SELECT ROW_NUMBER() over(order by s.schema_id) RowID,s.name

FROM sys.tables t

INNER JOIN sys.schemas s ON t.schema_id = s.schema_id

WHERE t.name = @tableName

) as v

where RowID=@schemaNameCount

–Declare a cursor to retrieve column specific information

–for the specified table

DECLARE cursCol CURSOR FAST_FORWARD

FOR

SELECT column_name ,

data_type

FROM information_schema.columns

WHERE table_name = @tableName

AND table_schema = @schemaName

OPEN cursCol

SET @string = 'INSERT INTO [' + @schemaName + '].[' + @tableName + ']('

SET @stringData = ''

DECLARE @colName NVARCHAR()

FETCH NEXT FROM cursCol INTO @colName, @dataType

PRINT @schemaName

PRINT @colName

IF @@fetch_status <>

BEGIN

PRINT 'Table ' + @tableName + ' not found, processing skipped.'

CLOSE curscol

DEALLOCATE curscol

RETURN

END

WHILE @@FETCH_STATUS =

BEGIN

IF @dataType IN ( 'varchar', 'char', 'nchar', 'nvarchar' )

BEGIN

SET @stringData = @stringData + '''''''''+

isnull(' + @colName + ','''')+'''''',''+'

END

ELSE

IF @dataType IN ( 'text', 'ntext' ) –if the datatype

–is text or something else

BEGIN

SET @stringData = @stringData + '''''''''+

isnull(cast(' + @colName + ' as nvarchar(max)),'''')+'''''',''+'

END

ELSE

IF @dataType = 'money' –because money doesn't get converted

–from varchar implicitly

BEGIN

SET @stringData = @stringData

+ '''convert(money,''''''+

isnull(cast(' + @colName

+ ' as nvarchar(max)),''.'')+''''''),''+'

END

ELSE

IF @dataType = 'datetime'

BEGIN

SET @stringData = @stringData

+ '''convert(datetime,''''''+

isnull(cast(' + @colName + ' as nvarchar(max)),'''')+''''''),''+'

END

ELSE

IF @dataType = 'image'

BEGIN

SET @stringData = @stringData + '''''''''+

isnull(cast(convert(varbinary,' + @colName + ')

as varchar()),'''')+'''''',''+'

END

ELSE –presuming the data type is int,bit,numeric,decimal

BEGIN

SET @stringData = @stringData + '''''''''+

isnull(cast(' + @colName + ' as nvarchar(max)),'''')+'''''',''+'

END

SET @string = @string + '[' + @colName + ']' + ','

FETCH NEXT FROM cursCol INTO @colName, @dataType

END

–After both of the clauses are built, the VALUES clause contains a trailing comma which needs to be replaced with a single quote. The prefixed clause will only face removal of the trailing comma.

DECLARE @Query NVARCHAR(MAX) — provide for the whole query,

— you may increase the size

PRINT @whereClause

IF ( @whereClause IS NOT NULL

AND @whereClause <> ''

)

BEGIN

SET @query = 'SELECT ''' + SUBSTRING(@string, , LEN(@string))

+ ') VALUES(''+ ' + SUBSTRING(@stringData, ,

LEN(@stringData) – )

+ '''+'')''

FROM ' +@schemaName+'.'+ @tableName + ' WHERE ' + @whereClause

PRINT @query

— EXEC sp_executesql @query –load and run the built query

–Eventually, close and de-allocate the cursor created for columns information.

END

ELSE

BEGIN

SET @query = 'SELECT ''' + SUBSTRING(@string, , LEN(@string))

+ ') VALUES(''+ ' + SUBSTRING(@stringData, ,

LEN(@stringData) – )

+ '''+'')''

FROM ' + @schemaName+'.'+ @tableName

END

CLOSE cursCol

DEALLOCATE cursCol

SET @schemaNameCount=@schemaNameCount-

IF(@schemaNameCount=)

BEGIN

SET @QueryString=@QueryString+@query

END

ELSE

BEGIN

SET @QueryString=@QueryString+@query+' UNION ALL '

END

PRINT convert(varchar(max),@schemaNameCount)+'—'+@QueryString

END

EXEC sp_executesql @QueryString –load and run the built query

–Eventually, close and de-allocate the cursor created for columns information.

这里要声明一下,如果你有多个schema,并且每个schema下面都有同一张表,那么脚本只会生成其中一个schema下面的表insert脚本

比如我现在有三个schema,下面都有customer这个表

?

1

2

3

4

5

CREATE TABLE dbo.[customer](city int,region int)

CREATE SCHEMA test

CREATE TABLE test.[customer](city int,region int)

CREATE SCHEMA test1

CREATE TABLE test1.[customer](city int,region int)

在执行脚本的时候他只会生成dbo这个schema下面的表insert脚本

?

1

INSERT INTO [dbo].[customer]([city],[region]) VALUES('1','2')

这个脚本有一个缺陷

无论你的表的字段是什麽数据类型,导出来的时候只能是字符

表结构

?

1

CREATE TABLE [dbo].[customer](city int,region int)

导出来的insert脚本

?

1

INSERT INTO [dbo].[customer]([city],[region]) VALUES('1','2')

我这里演示一下怎麽用

有两种方式

1、导全表数据

?

1

InsertGenerator 'customer', null

?

1

InsertGenerator 'customer', ' '

将表里的数据批量生成INSERT语句的存储过程 增强版

2、根据查询条件导数据

?

1

InsertGenerator 'customer', 'city=3'

或者

?

1

InsertGenerator 'customer', 'city=3 and region=8'

将表里的数据批量生成INSERT语句的存储过程 增强版

点击一下,选择全部

将表里的数据批量生成INSERT语句的存储过程 增强版

然后复制

将表里的数据批量生成INSERT语句的存储过程 增强版

新建一个查询窗口,然后粘贴

将表里的数据批量生成INSERT语句的存储过程 增强版

其实SQLServer的技巧有很多

最后,大家可以看一下代码,非常简单,如果要支持SQLServer2000,只要改一下代码就可以了

补充:创建一张测试表

?

1

2

3

4

5

6

7

8

9

CREATE TABLE testinsert (id INT,name VARCHAR(100),cash MONEY,dtime DATETIME)

INSERT INTO [dbo].[testinsert]

( [id], [name], [cash], [dtime] )

VALUES ( 1, — id – int

'nihao', — name – varchar(100)

8.8, — cash – money

GETDATE() — dtime – datetime

)

SELECT * FROM [dbo].[testinsert]

测试

?

1

2

3

InsertGenerator 'testinsert' ,''

InsertGenerator 'testinsert' ,'name=''nihao'''

InsertGenerator 'testinsert' ,'name=''nihao'' and cash=8.8'

datetime类型会有一些问题

生成的结果会自动帮你转换

?

1

INSERT INTO [dbo].[testinsert]([id],[name],[cash],[dtime]) VALUES('1','nihao',convert(money,'8.80'),convert(datetime,'02 8 2015 5:17PM'))

——————————————————————————–

群里的人共享的另一个脚本

?

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

IF OBJECT_ID('spGenInsertSQL','P') IS NOT NULL

DROP PROC spGenInsertSQL

GO

CREATE proc spGenInsertSQL (@tablename varchar(256),@number BIGINT,@whereClause NVARCHAR(MAX))

as

begin

declare @sql varchar(8000)

declare @sqlValues varchar(8000)

set @sql =' ('

set @sqlValues = 'values (''+'

select @sqlValues = @sqlValues + cols + ' + '','' + ' ,@sql = @sql + '[' + name + '],'

from

(select case

when xtype in (48,52,56,59,60,62,104,106,108,122,127)

then 'case when '+ name +' is null then ''NULL'' else ' + 'cast('+ name + ' as varchar)'+' end'

when xtype in (58,61,40,41,42)

then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast('+ name +' as varchar)'+ '+'''''''''+' end'

when xtype in (167)

then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'

when xtype in (231)

then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'

when xtype in (175)

then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar) + '))+'''''''''+' end'

when xtype in (239)

then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar) + '))+'''''''''+' end'

else '''NULL'''

end as Cols,name

from syscolumns

where id = object_id(@tablename)

) T

IF (@number!=0 AND @number IS NOT NULL)

BEGIN

set @sql ='select top '+ CAST(@number AS VARCHAR(6000))+' ''INSERT INTO ['+ @tablename + ']' + left(@sql,len(@sql)-1)+') ' + left(@sqlValues,len(@sqlValues)-4) + ')'' from '+@tablename

print @sql

END

ELSE

BEGIN

set @sql ='select ''INSERT INTO ['+ @tablename + ']' + left(@sql,len(@sql)-1)+') ' + left(@sqlValues,len(@sqlValues)-4) + ')'' from '+@tablename

print @sql

END

PRINT @whereClause

IF ( @whereClause IS NOT NULL AND @whereClause <> '')

BEGIN

set @sql =@sql+' where '+@whereClause

print @sql

END

exec (@sql)

end

GO

调用示例

?

1

2

3

4

5

6

7

8

9

10

11

12

–非dbo默认架构需注意

–支持数据类型 :bigint,int, bit,char,datetime,date,time,decimal,money, nvarchar(50),tinyint, nvarchar(max),varchar(max),datetime2

–调用示例 如果top行或者where条件为空,只需要把参数填上null

spGenInsertSQL 'customer' –表名

, 2 –top 行数

, 'city=3 and didian=''大连'' ' –where 条件

–导出全表 where条件为空

spGenInsertSQL 'customer' –表名

, null –top 行数

,null –where 条件

INSERT INTO [Department] ([DepartmentID],[Name],[GroupName],[Company],[ModifiedDate]) values (1,N'售后部',N'销售组',N'中国你好有限公司XX分公司','05 5 2015 5:58PM')

INSERT INTO [Department] ([DepartmentID],[Name],[GroupName],[Company],[ModifiedDate]) values (2,N'售后部',N'销售组',N'中国你好有限公司XX分公司','05 5 2015 5:58PM')

以上所述是本文给大家分享的将表里的数据批量生成INSERT语句的存储过程 增强版,希望大家喜欢。

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

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

软件定制开发公司

相关阅读

发表回复

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