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

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

目前sql server里面是没有相关的工具根据查询条件来生成insert语句的，只有借助第三方工具（third party tools）

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

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

这里有一个存储过程（适用于sqlserver2005 或以上版本）

-- 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这个表

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脚本

insert into [dbo].[customer]([city],[region]) values('1','2') 

这个脚本有一个缺陷

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

表结构

create table [dbo].[customer](city int,region int) 

导出来的insert脚本

insert into [dbo].[customer]([city],[region]) values('1','2') 

我这里演示一下怎麽用

有两种方式

1、导全表数据

insertgenerator 'customer', null 

或

insertgenerator 'customer', ' ' 

2、根据查询条件导数据

insertgenerator 'customer', 'city=3' 

或者

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

点击一下，选择全部

然后复制

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

其实sqlserver的技巧有很多

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

补充：创建一张测试表

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] 

测试

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

datetime类型会有一些问题

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

insert into [dbo].[testinsert]([id],[name],[cash],[dtime]) values('1','nihao',convert(money,'8.80'),convert(datetime,'02 8 2015 5:17pm')) 

--------------------------------------------------------------------------------

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

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 

调用示例

--非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语句的存储过程 增强版，希望大家喜欢。