批量生成insert 语句的存储过程

今天做了一个将表中数据导出，生成xml文件的需求。在网上找了几个存储过程：
生成表中所有数据的insert 语句的存储过程：

--exec spGenInsertSQL 'tabelname'
create proc [dbo].[spGenInsertSQL] (@tablename varchar(256))
as
begin
declare @sql varchar(8000)
declare @sqlValues varchar(8000)
set @sql =' ('
set @sqlValues = 'values (''+'
select @sqlValues = @sqlValues + cols + ' + '','' + ' ,@sql = @sql + '' + name + ','
    from
        (select case
                  when xtype in (48,52,56,59,60,62,104,106,108,122,127)                               
                       then 'case when '+ name +' is null then ''NULL''                      else ' + 'cast('+ name + ' as varchar)'+' end'
                  when xtype in (58,61)
                       then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast('+ name +' as varchar)'+ '+'''''''''+' end'
                 when xtype in (167)
                       then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'
                  when xtype in (231)
                       then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'
                  when xtype in (175)
                       then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar) + '))+'''''''''+' end'
                  when xtype in (239)
                       then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar) + '))+'''''''''+' end'
                  else '''NULL'''
                end as Cols,name
           from syscolumns 
          where id = object_id(@tablename)
        ) T
print @sqlValues
set @sql ='select ''INSERT INTO '+ @tablename + left(@sql,len(@sql)-1)+') ' + left(@sqlValues,len(@sqlValues)-4) + ')'' as sql from '+@tablename

print @sql
exec (@sql)
end

批量生成查询条件下的insert语句：

/*
Authore : neeraj prasad sharma (please dont remove this :))
Example (1) Exec [dbo].[INS]  'Dbo.test where 1=1'
        (2) Exec [dbo].[INS]  'Dbo.test where name =''neeraj''' * for string
	 (3) Exec [dbo].[INS]  'Dbo.test where createdate>''2013-09-20'''

here Dbo is schema and test is tablename and 1=1 is condition

*/


ALTER procedure  [dbo].[INS]                              
(                                                          
   @Query  Varchar(MAX)                                                          
)                              

AS                              

       Set nocount ON                  

DEclare @WithStrINdex as INT                            
DEclare @WhereStrINdex as INT                            
DEclare @INDExtouse as INT                            

Declare @SchemaAndTAble VArchar(270)                            
Declare @Schema_name  varchar(30)                            
Declare @Table_name  varchar(240)                            
declare @Condition  Varchar(MAX)                             

SET @WithStrINdex=0                            

SELECT @WithStrINdex=CHARINDEX('With',@Query )                            
, @WhereStrINdex=CHARINDEX('WHERE', @Query)                            

IF(@WithStrINdex!=0)                            
Select @INDExtouse=@WithStrINdex                            
ELSE                            
Select @INDExtouse=@WhereStrINdex                            

Select @SchemaAndTAble=Left (@Query,@INDExtouse-1)                                                     
select @SchemaAndTAble=Ltrim (Rtrim( @SchemaAndTAble))                            

Select @Schema_name= Left (@SchemaAndTAble, CharIndex('.',@SchemaAndTAble )-1)                            
,      @Table_name = SUBSTRING(  @SchemaAndTAble , CharIndex('.',@SchemaAndTAble )+1,LEN(@SchemaAndTAble) )                            

,      @CONDITION=SUBSTRING(@Query,@WhereStrINdex+6,LEN(@Query))--27+6                            


Declare   @COLUMNS  table (Row_number SmallINT , Column_Name VArchar(Max) )                              
Declare @CONDITIONS as varchar(MAX)                              
Declare @Total_Rows as SmallINT                              
Declare @Counter as SmallINT              

declare @ComaCol as varchar(max)            
select @ComaCol=''                   

Set @Counter=1                              
set @CONDITIONS=''                              

INsert INTO @COLUMNS                              
Select  Row_number()Over (Order by ORDINAL_POSITION ) [Count] ,Column_Name FRom INformation_schema.columns Where Table_schema=@Schema_name                              
And table_name=@Table_name         
and Column_Name not in ('SyncDestination','PendingSyncDestination' ,'SkuID','SaleCreditedto')                  

select @Total_Rows= Count(1) FRom  @COLUMNS                              

             Select @Table_name= '['+@Table_name+']'                      

             Select @Schema_name='['+@Schema_name+']'                      

While (@Counter<=@Total_Rows )                              
begin                               
--PRINT @Counter                              

    select @ComaCol= @ComaCol+'['+Column_Name+'],'            
    FROM @COLUMNS                              
Where [Row_number]=@Counter                          

select @CONDITIONS=@CONDITIONS+ ' +Case When ['+Column_Name+'] is null then ''Null'' Else ''''''''+                              

 Replace( Convert(varchar(Max),['+Column_Name+']  ) ,'''''''',''''  )                              

  +'''''''' end+'+''','''                              

FROM @COLUMNS                              
Where [Row_number]=@Counter                              

SET @Counter=@Counter+1                              

End                              

select @CONDITIONS=Right(@CONDITIONS,LEN(@CONDITIONS)-2)                              

select @CONDITIONS=LEFT(@CONDITIONS,LEN(@CONDITIONS)-4)              
select @ComaCol= substring (@ComaCol,0,  len(@ComaCol) )                            

select @CONDITIONS= '''INSERT INTO '+@Schema_name+'.'+@Table_name+ '('+@ComaCol+')' +' Values( '+'''' + '+'+@CONDITIONS                              

select @CONDITIONS=@CONDITIONS+'+'+ ''')'''                              

Select @CONDITIONS= 'Select  '+@CONDITIONS +'FRom  ' +@Schema_name+'.'+@Table_name+' With(NOLOCK) ' + ' Where '+@Condition                              
print(@CONDITIONS)                              
Exec(@CONDITIONS)