求一个把查询结果导出为insert脚本的小工具 最好不要用存储过程实现的我以前用过一个小巧的sqlsever连接工具,在查询出来的结果上右键点击,其中一项就是把结果导出为一堆的insert脚本可是忘记名字了,哪位能再提供以下啊 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 CREATE proc dbo.proc_insert (@tablename varchar(256),@colid int=1)/*输入:(必选)Table名称, varchar (可选)从第几个栏位开始生成(比如id自增型的栏位就不需要显式insert),int输出:产生的insert语句,每行一条。说明:请勿在查询分析器里使用时,有时会得到被截断的语句。解决方法:查询分析器->工具->选项->结果->每列最多字符数(修改)*/asbegin set nocount on declare @sqlstr varchar(8000) declare @sqlstr1 varchar(8000) declare @sqlstr2 varchar(8000) select @sqlstr='select ''insert '+@tablename select @sqlstr1='' select @sqlstr2=' (' select @sqlstr1= ' values ( ''+' select @sqlstr1 = @sqlstr1 + col + '+'',''+',@sqlstr2 = @sqlstr2 + name + ',' from (select case -- when a.xtype =173 then 'case when '+'['+a.name+']'+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+'['+a.name+']' +')'+' end' when a.xtype = 104 then 'case when ' + '[' + a.name + ']' + ' is null then ''NULL'' else ' + 'convert(varchar(1),' + '[' + a.name + ']' + ')' + ' end' when a.xtype = 175 then 'case when ' + '[' + a.name + ']' + ' is null then ''NULL'' else ' + '''''''''+' + 'replace(' + '[' + a.name + ']' + ','''''''','''''''''''')' + '+''''''''' + ' end' when a.xtype = 61 then 'case when ' + '[' + a.name + ']' + ' is null then ''NULL'' else ' + '''''''''+' + 'convert(varchar(23),' + '[' + a.name + ']' + ',121)' + '+''''''''' + ' end' when a.xtype = 106 then 'case when ' + '[' + a.name + ']' + ' is null then ''NULL'' else ' + 'convert(varchar(' + convert(varchar(4), a.xprec + 2) + '),' + '[' + a.name + ']' + ')' + ' end' when a.xtype = 62 then 'case when ' + '[' + a.name + ']' + ' is null then ''NULL'' else ' + 'convert(varchar(23),' + '[' + a.name + ']' + ',2)' + ' end' when a.xtype = 56 then 'case when ' + '[' + a.name + ']' + ' is null then ''NULL'' else ' + 'convert(varchar(11),' + '[' + a.name + ']' + ')' + ' end' when a.xtype = 127 then 'case when ' + '[' + a.name + ']' + ' is null then ''NULL'' else ' + 'convert(varchar(19),' + '[' + a.name + ']' + ')' + ' end' when a.xtype = 60 then 'case when ' + '[' + a.name + ']' + ' is null then ''NULL'' else ' + 'convert(varchar(22),' + '[' + a.name + ']' + ')' + ' end' when a.xtype = 239 then 'case when ' + '[' + a.name + ']' + ' is null then ''NULL'' else ' + '''''''''+' + 'replace(' + '[' + a.name + ']' + ','''''''','''''''''''')' + '+''''''''' + ' end' when a.xtype = 108 then 'case when ' + '[' + a.name + ']' + ' is null then ''NULL'' else ' + 'convert(varchar(' + convert(varchar(4), a.xprec + 2) + '),' + '[' + a.name + ']' + ')' + ' end' when a.xtype = 231 then 'case when ' + '[' + a.name + ']' + ' is null then ''NULL'' else ' + '''''''''+' + 'replace(' + '[' + a.name + ']' + ','''''''','''''''''''')' + '+''''''''' + ' end' when a.xtype = 59 then 'case when ' + '[' + a.name + ']' + ' is null then ''NULL'' else ' + 'convert(varchar(23),' + '[' + a.name + ']' + ',2)' + ' end' when a.xtype = 58 then 'case when ' + '[' + a.name + ']' + ' is null then ''NULL'' else ' + '''''''''+' + 'convert(varchar(23),' + '[' + a.name + ']' + ',121)' + '+''''''''' + ' end' when a.xtype = 52 then 'case when ' + '[' + a.name + ']' + ' is null then ''NULL'' else ' + 'convert(varchar(12),' + '[' + a.name + ']' + ')' + ' end' when a.xtype = 122 then 'case when ' + '[' + a.name + ']' + ' is null then ''NULL'' else ' + 'convert(varchar(22),' + '[' + a.name + ']' + ')' + ' end' when a.xtype = 48 then 'case when ' + '[' + a.name + ']' + ' is null then ''NULL'' else ' + 'convert(varchar(6),' + '[' + a.name + ']' + ')' + ' end' -- when a.xtype =165 then 'case when '+'['+a.name+']'+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+'['+a.name+']' +')'+' end' when a.xtype = 167 then 'case when ' + '[' + a.name + ']' + ' is null then ''NULL'' else ' + '''''''''+' + 'replace(' + '[' + a.name + ']' + ','''''''','''''''''''')' + '+''''''''' + ' end' else '''NULL''' end as col, a.colid, '[' + a.name + ']' as name from syscolumns a where a.colid >= @colid and a.id = object_id(@tablename) and a.xtype <> 189 and a.xtype <> 34 and a.xtype <> 35 and a.xtype <> 36 and a.autoval is null) t order by colid select @sqlstr = @sqlstr + left(@sqlstr2, len(@sqlstr2) - 1) + ') ' + left(@sqlstr1, len(@sqlstr1) - 3) + ')'' from ' + @tablename -- print @sqlstr exec(@sqlstr) set nocount offENDGO 调用proc_insert 'tb' 关于问答系统的数据库设计问题 新手提问:为什么捕捉不到@@ERROR 请教:如何用触发器实现:防止更新已作废数据? 如何写SQL语句将1234567变成1,234,567 Win7旗舰版新系统,无法连接Sybase 请问怎么解答这个问题 在存储过程中,当执行一条select语句时,如何把数据库引擎检索出查询结果放入变更中 求高手解答sql定时执行问题 存储过程的几个问题,大家请进来瞧瞧! [求助],SQL SERVER 有一个数据表老被阻塞,一句Update都要200秒, 两个表格的连接 sql行专列问题
/*
输入:(必选)Table名称, varchar
(可选)从第几个栏位开始生成(比如id自增型的栏位就不需要显式insert),int
输出:产生的insert语句,每行一条。
说明:请勿在查询分析器里使用时,有时会得到被截断的语句。解决方法:查询分析器->工具->选项->结果->每列最多字符数(修改)*/
as
begin
set nocount on
declare @sqlstr varchar(8000)
declare @sqlstr1 varchar(8000)
declare @sqlstr2 varchar(8000)
select @sqlstr='select ''insert '+@tablename
select @sqlstr1=''
select @sqlstr2=' ('
select @sqlstr1= ' values ( ''+'
select @sqlstr1 = @sqlstr1 + col + '+'',''+',@sqlstr2 = @sqlstr2 + name + ','
from (select case
-- when a.xtype =173 then 'case when '+'['+a.name+']'+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+'['+a.name+']' +')'+' end'
when a.xtype = 104 then
'case when ' + '[' + a.name + ']' +
' is null then ''NULL'' else ' + 'convert(varchar(1),' + '[' +
a.name + ']' + ')' + ' end'
when a.xtype = 175 then
'case when ' + '[' + a.name + ']' +
' is null then ''NULL'' else ' + '''''''''+' + 'replace(' + '[' +
a.name + ']' + ','''''''','''''''''''')' + '+''''''''' +
' end'
when a.xtype = 61 then
'case when ' + '[' + a.name + ']' +
' is null then ''NULL'' else ' + '''''''''+' +
'convert(varchar(23),' + '[' + a.name + ']' + ',121)' +
'+''''''''' + ' end'
when a.xtype = 106 then
'case when ' + '[' + a.name + ']' +
' is null then ''NULL'' else ' + 'convert(varchar(' +
convert(varchar(4), a.xprec + 2) + '),' + '[' + a.name + ']' + ')' +
' end'
when a.xtype = 62 then
'case when ' + '[' + a.name + ']' +
' is null then ''NULL'' else ' + 'convert(varchar(23),' + '[' +
a.name + ']' + ',2)' + ' end'
when a.xtype = 56 then
'case when ' + '[' + a.name + ']' +
' is null then ''NULL'' else ' + 'convert(varchar(11),' + '[' +
a.name + ']' + ')' + ' end'
when a.xtype = 127 then
'case when ' + '[' + a.name + ']' +
' is null then ''NULL'' else ' + 'convert(varchar(19),' + '[' +
a.name + ']' + ')' + ' end'
when a.xtype = 60 then
'case when ' + '[' + a.name + ']' +
' is null then ''NULL'' else ' + 'convert(varchar(22),' + '[' +
a.name + ']' + ')' + ' end'
when a.xtype = 239 then
'case when ' + '[' + a.name + ']' +
' is null then ''NULL'' else ' + '''''''''+' + 'replace(' + '[' +
a.name + ']' + ','''''''','''''''''''')' + '+''''''''' +
' end'
when a.xtype = 108 then
'case when ' + '[' + a.name + ']' +
' is null then ''NULL'' else ' + 'convert(varchar(' +
convert(varchar(4), a.xprec + 2) + '),' + '[' + a.name + ']' + ')' +
' end'
when a.xtype = 231 then
'case when ' + '[' + a.name + ']' +
' is null then ''NULL'' else ' + '''''''''+' + 'replace(' + '[' +
a.name + ']' + ','''''''','''''''''''')' + '+''''''''' +
' end'
when a.xtype = 59 then
'case when ' + '[' + a.name + ']' +
' is null then ''NULL'' else ' + 'convert(varchar(23),' + '[' +
a.name + ']' + ',2)' + ' end'
when a.xtype = 58 then
'case when ' + '[' + a.name + ']' +
' is null then ''NULL'' else ' + '''''''''+' +
'convert(varchar(23),' + '[' + a.name + ']' + ',121)' +
'+''''''''' + ' end'
when a.xtype = 52 then
'case when ' + '[' + a.name + ']' +
' is null then ''NULL'' else ' + 'convert(varchar(12),' + '[' +
a.name + ']' + ')' + ' end'
when a.xtype = 122 then
'case when ' + '[' + a.name + ']' +
' is null then ''NULL'' else ' + 'convert(varchar(22),' + '[' +
a.name + ']' + ')' + ' end'
when a.xtype = 48 then
'case when ' + '[' + a.name + ']' +
' is null then ''NULL'' else ' + 'convert(varchar(6),' + '[' +
a.name + ']' + ')' + ' end'
-- when a.xtype =165 then 'case when '+'['+a.name+']'+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+'['+a.name+']' +')'+' end'
when a.xtype = 167 then
'case when ' + '[' + a.name + ']' +
' is null then ''NULL'' else ' + '''''''''+' + 'replace(' + '[' +
a.name + ']' + ','''''''','''''''''''')' + '+''''''''' +
' end'
else
'''NULL'''
end as col,
a.colid,
'[' + a.name + ']' as name
from syscolumns a
where a.colid >= @colid
and a.id = object_id(@tablename)
and a.xtype <> 189
and a.xtype <> 34
and a.xtype <> 35
and a.xtype <> 36
and a.autoval is null) t
order by colid
select @sqlstr = @sqlstr +
left(@sqlstr2, len(@sqlstr2) - 1) + ') ' +
left(@sqlstr1, len(@sqlstr1) - 3) + ')'' from ' + @tablename
-- print @sqlstr
exec(@sqlstr) set nocount off
END
GO
调用proc_insert 'tb'