createproc spGenInsertSQL @TableNameasvarchar(100) as --declare @TableName varchar(100) --set @TableName = 'orders' --set @TableName = 'eeducation' DECLARE xCursor CURSORFOR SELECT name,xusertype FROM syscolumns WHERE (id =OBJECT_ID(@TableName)) declare@F1varchar(100) declare@F2integer declare@SQLvarchar(8000) set@sql='SELECT ''INSERT INTO '+@TableName+' VALUES(''' OPEN xCursor FETCH xCursor into@F1,@F2 WHILE@@FETCH_STATUS=0 BEGIN set@sql=@sql+ +casewhen@F2IN (35,58,99,167,175,231,239,61) then' + case when '+@F1+' IS NULL then '''' else '''''''' end + 'else'+'end +'replace(ISNULL(cast('+@F1+' as varchar),''NULL''),'''''''','''''''''''')' +casewhen@F2IN (35,58,99,167,175,231,239,61) then' + case when '+@F1+' IS NULL then '''' else '''''''' end + 'else'+'end +char(13) +''',''' FETCHNEXTFROM xCursor into@F1,@F2 END CLOSE xCursor DEALLOCATE xCursor set@sql=left(@sql,len(@sql) -5) +' + '')'' FROM '+@TableName print@sql exec (@sql) 第二版:2003.03.08 alterproc SPGenInsertSQL (@tablenamevarchar(256)) as begin declare@sqlvarchar(8000) declare@sqlValuesvarchar(8000) set@sql=' (' set@sqlValues='values (''+' select@sqlValues=@sqlValues+ cols +' + '','' + ' ,@sql=@sql+'['+ name +'],' from (selectcase 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 asvarchar) +'))+'''''''''+' end' when xtype in (239) then'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + '+'cast(replace('+ name+','''''''','''''''''''') as Char('+cast(length asvarchar) +'))+'''''''''+' end' else'''NULL''' endas Cols,name from syscolumns where id =object_id(@tablename) ) T set@sql='select ''INSERT INTO ['+@tablename+']'+left(@sql,len(@sql)-1)+') '+left(@sqlValues,len(@sqlValues)-4) +')'' from '+@tablename --print @sql exec (@sql) end 第三版: 2003.3.9 ALTERproc SPGenInsertSQL (@tablenamevarchar(256)) as begin declare@sqlvarchar(8000) declare@sqlValuesvarchar(8000) set@sql=' (' set@sqlValues='values (''+' select@sqlValues=@sqlValues+ cols +' + '','' + ' ,@sql=@sql+'['+ name +'],' from (selectcase 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,175) then'case when '+ name +' is null then ''NULL'' else '+''''''''' + '+'replace('+ name+','''''''','''''''''''')'+'+'''''''''+' end' when xtype in (231,239) then'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + '+'replace('+ name+','''''''','''''''''''')'+'+'''''''''+' end' else'''NULL''' endas Cols,name from syscolumns where id =object_id(@tablename) and autoval isnull ) T set@sql='select ''INSERT INTO ['+@tablename+']'+left(@sql,len(@sql)-1)+') '+left(@sqlValues,len(@sqlValues)-4) +')'' from '+@tablename print@sql exec (@sql) /**//* select * from syscolumns where id = object_id('test') and autoval is null */ end