服务器之家：专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Sql Server - 将表里的数据批量生成INSERT语句的存储过程 增强版

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

2020-04-30 14:21桦仔 Sql Server

这篇文章主要介绍了将表里的数据批量生成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语句的存储过程 增强版，希望大家喜欢。

  • 批量
  • 存储过程
  • insert

    • 延伸 · 阅读

    精彩推荐
    最近更新
    编辑推荐
    阅读排行
    热门标签
    2005 　 18052 　 3456 　 1355 　 29506 　 9003 　 5120 　 21002 　 5173 　 15138 　 15404 　 功能语句 　 去零函数 　 from 　 字符串聚合 　 备份记录 　 增长情况 　 多行记录 　 逻辑查询 　 实用语句 　 数据一致性 　 日期查询 　 字段编号 　 约束增强 　 行列转换 　 @@ERROR 　 日志备份 　 分组统计 　 已经存在 　 numeric 　
    © 2019-2022 服务器之家(www.tuohang.net) 版权所有关于我们联系我们版权申明网站地图