生成数据脚本

[晓风残月0110]

各位请教一下数据脚本生成的proc
最近很忙大家帮弄弄

原版

create procedure   [dbo].[Sys_GetData]     
@tablename   sysname      
AS      
declare   @column   varchar(1000)     
declare   @columndata   varchar(1000)     
declare   @sql   varchar(4000)     
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  @tablename +  '对象不存在'     
  return      
end     
set @objectname=rtrim(object_name(@objectId))     
if @objectname is null or charindex(@objectname,@tablename)=0
begin      
  print  @tablename +  '对象不在当前数据库中'      
  return      
end         
if  OBJECTPROPERTY(@objectId,'IsTable')   <   >   1   --   判断对象是否是表   
begin      
  print  @tablename +  '对象不是表'     
  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'     
--定义游标，循环取数据并生成Insert语句
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'  

需要大家修改的问题

1 如果表包含自增列，在生成的insert语句中去掉这列
2 特殊字段的生成，上例中不能生成text,ntext的，希望更改成可以生成text,ntext的
3 如果text内容很长，保证可以把数据正确取出

[wzy_love_sly]

05吗

[liangCK]

帮顶.接分.

[liangCK]

建议使用zj老大资源里的生成insert数据的程序..

[水族杰纶]

帮顶
............

[晓风残月0110]

sql 2000

紫煈又升了，这里人气一直很旺啊

to:liangCK
什么程序？

[wzy_love_sly]

if object_id('dbo.proc_insert') is not null
drop proc dbo.proc_insert
go
create proc proc_insert (@tablename varchar(256))  
 as
 begin
  set nocount on  
  declare @sqlstr varchar(4000)  
  declare @sqlstr1 varchar(4000)  
  declare @sqlstr2 varchar(4000)  
  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 =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  
  -- print @sqlstr  
  exec( @sqlstr)  
  set nocount off  
 end   

go
--测试键表
create table tb(id int,name varchar(50))
insert into tb select 1,'abc'
insert into tb select 2,'abc'
insert into tb select 3,'abc'
insert into tb select 4,'abc'
--执行过程，形成insert语句
exec dbo.proc_insert 'tb'

[ws_hgo]

6楼的升了居然不散分

[liangCK]

[Quote=引用 5 楼 charry0110 的回复:]
sql 2000

紫煈又升了，这里人气一直很旺啊

to:liangCK
什么程序？
[/Quote]

zj老大资源里的..

[wzy_love_sly]

没什么时间上拉 呵呵，修改一下估计就可以了

[Andy-W]

Use Test
Go
Declare 
        @Table1 nvarchar(128),
        @Table2 nvarchar(128),
        @Sql1 nvarchar(4000),
        @Sql2 nvarchar(4000),
        @SqlIdentityInsert nvarchar(512)

Set @Table1='DutyHistory'    --源表
Set @Table2='Duty'            --目标表

IF Object_id(@Table1,'U') Is Null 
    Return
If Isnull(@Table2,'')=''
    Set @Table2=@Table1

Set @SqlIdentityInsert=''
If Exists(Select 1 From sys.columns Where Object_id=Object_id(@Table1,'U'))
    Set @SqlIdentityInsert='Select ''Set Identity_Insert '+@Table2+' On '''

Select 
    @Sql1=Isnull(@Sql1+',',' Insert Into '+@Table2+' (')+Quotename(name),
    @Sql2=Isnull(@Sql2+'+'',''+','Select @Sql1+'' Select ''')+'+Case When '+Quotename(name)+' Is null Then ''null'' Else '+
            Case When user_type_id In(175,61,239,231,58,98,36,167,241) Then '''''''''+Rtrim('+Quotename(name)+')+''''''''' Else 'Rtrim('+Quotename(name)+')' End +' End'
From sys.columns 
Where Object_id=Object_id(@Table1,'U')

Set @Sql1=@Sql1+')'
Set @Sql2=    'Select Convert(nvarchar(max),''If Object_id('''''+@Table2+''''',''''U'''') Is Null Return;'') As SqlInsert  Union All '+
            @SqlIdentityInsert+' Union All '+
            @Sql2+' From '+Quotename(@Table1)+Case @SqlIdentityInsert When '' Then '' Else ' Union All ' End+
            Replace(@SqlIdentityInsert,' On ',' Off')

Exec sp_executesql @Sql2,N'@Sql1 nvarchar(4000)',@Sql1

Result:

SqlInsert
---------------------------------------------------------
If Object_id('Duty','U') Is Null Return;
Set Identity_Insert Duty On
 Insert Into Duty ([id],[Name],[WorkDate],[WorkHours]) Select 1,'Robert','01  1 2008 12:00AM',3.4
 Insert Into Duty ([id],[Name],[WorkDate],[WorkHours]) Select 2,'Robert','01  2 2008 12:00AM',3.4
 Insert Into Duty ([id],[Name],[WorkDate],[WorkHours]) Select 3,'Robert','01  3 2008 12:00AM',3.4
... ...
 Insert Into Duty ([id],[Name],[WorkDate],[WorkHours]) Select 58,'Robert','02 27 2008 12:00AM',4.5
 Insert Into Duty ([id],[Name],[WorkDate],[WorkHours]) Select 59,'Robert','02 28 2008 12:00AM',4.5
 Insert Into Duty ([id],[Name],[WorkDate],[WorkHours]) Select 60,'Robert','02 29 2008 12:00AM',4.5
Set Identity_Insert Duty Off

[liangCK]

[liangCK]

这个程序生成的insert语句很快...

[晓风残月0110]

脚本容易控制些


紫煈的脚本


1 没有处理自增列的问题
2 没有处理text，ntext的问题
3 在我这有一个表没过去

消息 102，级别 15，状态 1，第 1 行
Incorrect syntax near 'null'.
消息 102，级别 15，状态 1，第 1 行
Incorrect syntax near 'c'.


都要修改一下

[晓风残月0110]

to:

DVD_01


我的表多，不方便用你的方法

[晓风残月0110]

大家动动手，呜呜

[晓风残月0110]

支持一下

[pt1314917]

帮顶顶。我有个工具，可以直接将表中的数据生成insert语句。楼主要就M我下。

[elvis_gao]

帮哥们顶，有难度

[elvis_gao]

友情up，有难度

[hyde100]

友情支持。。。