首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 计算机考试 > 等级考试 > 考试试题 >

如何为SQLServer表数据生成insert脚本(1)

2009-03-17 
使用SQL Server数据库自带的“生成SQL脚本”工具,可以生成创建表、视图、存储过程等的SQL脚本。

    那么,能否将表中的数据也生成为SQL脚本,在查询分析器中执行这些脚本后自动将数据导入到SQL Server中呢?答案是肯定的,示例如下:
  CREATE PROCEDURE dbo.OutputData
  @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'
  调用时 exec OutputData 'myuser' 其中myUser中当前数据库中存在的表。
  另外方丈的:
  drop proc 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

热点排行