这样的INSERT语句如何快速生成?

[flymore2013]

想通过数据库里现成的数据,快速生成INSERT语句.

如:
TEST_TABLE
---------------
cola,colb
1,a
2,b

期望能够生成以下语句
可以生成INSERT INTO TEST_TABLE(cola,colb) values(1,a)
INSERT INTO TEST_TABLE(cola,colb) values(2,b)
注:我数据库里数据表很多,行数据也很多!

[flymore2013]

注:有的字段是大文本字段,包含各种怪字符的,也要支持. 如:其中1行的1列的值如下: ------------------------------------------------------------ { columns: [ { display: "账号", name: "LoginName", width: 180, type: "text", align: "left" }, //{ display: "密码", name: "LoginPassword", width: 180, type: "text", align: "left" }, { display: "真实姓名", name: "RealName", width: 180, type: "text", align: "left" }, { display: "头衔", name: "Title", width: 180, type: "text", align: "left" }, { display: "性别", name: "Sex", width: 180, type: "text", align: "left" }, { display: "电话", name: "Phone", width: 180, type: "text", align: "left" }, { display: "传真", name: "Fax", width: 180, type: "text", align: "left" }, { display: "电子邮件", name: "Email", width: 180, type: "text", align: "left" }, { display: "QQ", name: "QQ", width: 180, type: "text", align: "left" }, { display: "昵称", name: "NickName", width: 180, type: "text", align: "left" }, { display: "地址", name: "Address", width: 180, type: "textarea", align: "left" } ] }

[Mr_Nice]

数据库（右键）——任务——生成脚本——选表... 即可。

[發糞塗牆]

试试：

 ALTER proc [dbo].[sp_CreateInsertScript] (@tablename varchar(256),@con nvarchar(400)) 
 as 
 begin 
 set nocount on 
 declare @sqlstr varchar(max) 
 declare @sqlstr1 varchar(max) 
 declare @sqlstr2 varchar(max) 
 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 =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 =127 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(6),'+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 
 from syscolumns a where a.id = object_id(@tablename) 
 and a.xtype <>189 and a.xtype <>34 and a.xtype <>35 and a.xtype <>36 
 )t order by colid 
 select @sqlstr=@sqlstr+left(@sqlstr2,len(@sqlstr2)-1)+') '+left(@sqlstr1,len(@sqlstr1)-3)+')'' from '+@tablename + ' where 1=1 and ' + isnull(@con,'') 
 print @sqlstr 
 exec( @sqlstr) 
 set nocount off 
 end
 

[flymore2012]

OrchidCat,你那个生成的是CREATE TABLE语句,不是INSERT语句. DBA_Huangzj,你那个生成普通的SQL可以,大文本字段不行.

[發糞塗牆]

那你把varchar那些换成nvarchar(max)试试。这个是我们公司用的。暂时没啥问题

[Mr_Nice]

存储过程导出insert语句

if exists (select * from sysobjects where type='p' and  name='UspOutputData')   
   drop proc UspOutputData   
GO   
CREATE PROCEDURE dbo.UspOutputData    
@tablename sysname    
AS    
declare @column varchar(2000)    
declare @columndata varchar(2000)    
declare @sql varchar(8000)    
declare @xtype tinyint    
declare @name sysname    
declare @objectId int    
declare @objectname sysname    
declare @ident int    
set nocount on    
-- 判斷對象是否存在    
set @objectId=object_id(@tablename)    
if @objectId is null    
begin    
print 'The object not exists'    
return    
end    
--此判断不严密    
set @objectname=rtrim(object_name(@objectId))    
if @objectname is null or charindex(@objectname,@tablename)=0    
begin    
print 'object not in current database'    
return    
end    
-- 判斷對象是否是table    
if OBJECTPROPERTY(@objectId,'IsTable') < > 1    
begin    
print 'The object is not table'    
return    
end    
--不知道打印的意义   
select @ident=status&0x80 from syscolumns where id=@objectid and status&0x80=0x80    
if @ident is not null    
print 'SET IDENTITY_INSERT '+@TableName+' ON'    
  
declare syscolumns_cursor cursor   
for select c.name,c.xtype from syscolumns c where c.id=@objectid order by c.colid    
open syscolumns_cursor    
set @column=''    
set @columndata=''    
fetch next from syscolumns_cursor into @name,@xtype    
while @@fetch_status < >-1    
  begin    
    if @@fetch_status < >-2    
      begin    
        --if @xtype not in(189,34,35,99,98) --timestamp不需处理，image,text,ntext,sql_variant 暂时不处理    
          begin    
            set @column=@column+case when len(@column)=0 then'' else ','end+@name     
            set @columndata=@columndata+case when len(@columndata)=0 then '' else ','','','end    
                +case when @xtype in(167,175) then '''''''''+'+@name+'+''''''''' --varchar,char    
                      when @xtype in(231,239) then '''N''''''+'+@name+'+''''''''' --nvarchar,nchar    
                      when @xtype=61 then '''''''''+convert(char(23),'+@name+',121)+''''''''' --datetime    
                      when @xtype=58 then '''''''''+convert(char(16),'+@name+',120)+''''''''' --smalldatetime    
                      when @xtype=36 then '''''''''+convert(char(36),'+@name+')+''''''''' --uniqueidentifier    
                      else @name end    
          end    
      end    
    fetch next from syscolumns_cursor into @name,@xtype    
end    
close syscolumns_cursor    
deallocate syscolumns_cursor    
set @sql='set nocount on select ''insert '+@tablename+'('+@column+') values(''as ''--'','+@columndata+','')'' from '+@tablename    
print '--'+@sql    
exec(@sql)    
--不知道打印的意义   
if @ident is not null    
print 'SET IDENTITY_INSERT '+@TableName+' OFF'    
GO   
  
exec UspOutputData Table_Name

[习惯性蹭分]

mark下，暂时还没看懂，以后研究下。

[jAmEs_]

帮顶，接分，前面已经有方案了

[99归一]

引用 3 楼 DBA_Huangzj 的回复:

试试：SQL code?12345678910111213141516171819202122232425262728293031323334353637383940414243 ALTER proc [dbo].[sp_CreateInsertScript] (@tablename varchar(256),@con nvarchar(400)) as begin ……

好东西，不错不错

[f152520843]

偶也Mark！