将表中的数据生成SQL脚本

转载:http://topic.csdn.net/u/20080310/09/f1c5c8d1-e60b-48ad-8068-fa5a58e59788.html

其功能是：将表中的数据生成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中当前数据库中存在的表