【转载】把SQL表数据数据转换为INSERT INTO语句的存储过程

　　今天因为要把大批量的数据转换成INSERT INTO 方式，方便同步用，可以那么多数据没办法一条一条的写，自己写了一个转换的感觉不大好用，于是网上看到一个很不错，分享给大家：

　　

USE [FTSDL2]
GO

/****** Object:  StoredProcedure [dbo].[OutputData]    Script Date: 12/01/2011 18:10:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

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   'The   object   not   exists'     
  return     
  End     
  Set     @objectname=object_name(@objectId)   
    
  if     @objectname   Is   null   Or   charindex(@objectname,@tablename)=0   --此判断不严密   
  begin   
  print   'object   not   in   current   database'   
  return   
  End   
    
  if   OBJECTPROPERTY(@objectId,'IsTable')   <>   1   --   判斷對象是否是table     
  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