这里用到了系统表syscolumns中的xtype字段，xtype具体含义如下：
34 image，35 text，36 uniqueidentifier，48 tinyint，52 smallint，56 int，58 smalldatetime，59 real，60 money，61 datetime，62 float，98 sql_variant，99 ntext，104 bit，106 decimal，108 numeric，122 smallmoney，127 bigint，165 varbinary，167 varchar，173 binary，175 char，189 timestamp，231 sysname，231 nvarchar，239 nchar。
利用这些数字识别将要插入字段的类型。用一下存储过程即可，注：该存储过程也将主键导出了。
-- ===========================
--Description: 该存储过程返回的是参数表中的数据
-- ============================
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Create proc [dbo].[xf_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 (36,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
set @sql ='select ''INSERT INTO ['+ @tablename + ']' + left(@sql,len(@sql)-1)+') ' + left(@sqlValues,len(@sqlValues)-4) + ')'' from '+@tablename
--print @sql
exec (@sql)
end
创建好存储过程后，执行一下就行了，不过这个不支持text字段的喔~
