批量生成insert 语句的存储过程
今天做了一个将表中数据导出，生成xml文件的需求。在网上找了几个存储过程：
生成表中所有数据的insert 语句的存储过程：
--exec spGenInsertSQL 'tabelname'create proc [dbo].[spGenInsertSQL] (@tablename varchar(256))asbegindeclare @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) ) Tprint @sqlValuesset @sql ='select ''INSERT INTO '+ @tablename + left(@sql,len(@sql)-1)+') ' + left(@sqlValues,len(@sqlValues)-4) + ')'' as sql from '+@tablenameprint @sqlexec (@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)