语句如下：
--1. 选中你要操作的数据库, 执行以下sql语句
IF OBJECT_ID('spGenInsertSQL','P') IS NOT NULL
DROP PROC spGenInsertSQL
GO
CREATE proc 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
set @sql ='select ''INSERT INTO ['+ @tablename + ']' + left(@sql,len(@sql)-1)+') ' + left(@sqlValues,len(@sqlValues)-4) + ')'' from [email protected]
print @sql
exec (@sql)
end
GO
-- 2. 执行成功以后,调用这个存储过程,要传一个参数: 表名
exec spGenInsertSQL '表名'
--- 执行后，生成这个表的插入语句，
生成其他表的插入记录时是没问题的。
但是，当生成表yy_sg.CS_TOBACCO_BIZ_AUDIT的插入记录时报错如下：
select 'INSERT INTO [yy_sg.CS_TOBACCO_BIZ_AUDIT] ([TOBACCO_BIZ_AUDIT_ID],[INVOICE_NO],[PERSON_NO],[PERSON_NAME],[WEIGHT],[AMOUNT],[ALLOWANCE_AMOUNT1],[ALLOWANCE_AMOUNT2],[DEDUCT_AMOUNT],[TRANSFER_AMOUNT],[TRANSFER_STYLE],[TRANSFER_STATUS],[AUDIT_IS_SUCC],[CHECK_RANK],[IS_FINAL_AUDIT],[AUDIT_OPERATOR_UUID],[AUDIT_OPERATOR_NAME],[INVOICING_STYLE],[INVOICING_DATE],[BUSINESS_YEAR],[ORG_CD],[SEND_STATE],[MODIFY_TIME],[SEND_TIME],[PACKAGE_TIME],[DATA_STATE],[LAST_TIME],[RESERVED],[RESERVED3],[TRANSFER_DATE],[CHECK_AMOUNT_STATUS],[CHECK_AMOUNT_INFO_CD],[TRANSFER_INFO_CD],[BANK_TRANSFER_DATE],[VERSION],[REVERSAL_STATUS],[REVERSAL_INVOICE_NO],[JHLX],[INVOICE_PRINT_STATE]) values ('+case when TOBACCO_BIZ_AUDIT_ID is null then 'NULL' else '''' + cast(replace(TOBACCO_BIZ_AUDIT_ID,'''','''''') as Char(32))+'''' end + ',' + case when INVOICE_NO is null then 'NULL' else '''' + replace(INVOICE_NO,'''','''''')+'''' end + ',' + case when PERSON_NO is null then 'NULL' else '''' + replace(PERSON_NO,'''','''''')+'''' end + ',' + case when PERSON_NAME is null then 'NULL' else '''' + replace(PERSON_NAME,'''','''''')+'''' end + ',' + case when WEIGHT is null then 'NULL' else cast(WEIGHT as varchar) end + ',' + case when AMOUNT is null then 'NULL' else cast(AMOUNT as varchar) end + ',' + case when ALLOWANCE_AMOUNT1 is null then 'NULL' else cast(ALLOWANCE_AMOUNT1 as varchar) end + ',' + case when ALLOWANCE_AMOUNT2 is null then 'NULL' else cast(ALLOWANCE_AMOUNT2 as varchar) end + ',' + case when DEDUCT_AMOUNT is null then 'NULL' else cast(DEDUCT_AMOUNT as varchar) end + ',' + case when TRANSFER_AMOUNT is null then 'NULL' else cast(TRANSFER_AMOUNT as varchar) end + ',' + case when TRANSFER_STYLE is null then 'NULL' else '''' + cast(replace(TRANSFER_STYLE,'''','''''') as Char(1))+'''' end + ',' + case when TRANSFER_STATUS is null then 'NULL' else '''' + cast(replace(TRANSFER_STATUS,'''','''''') as Char(1))+'''' end + ',' + case when AUDIT_IS_SUCC is null then 'NULL' else '''' + cast(replace(AUDIT_IS_SUCC,'''','''''') as Char(1))+'''' end + ',' + case when CHECK_RANK is null then 'NULL' else cast(CHECK_RANK as varchar) end + ',' + case when IS_FINAL_AUDIT is null then 'NULL' else '''' + cast(replace(IS_FINAL_AUDIT,'''','''''') as Char(1))+'''' end + ',' + case when AUDIT_OPERATOR_UUID is null then 'NULL' else '''' + replace(AUDIT_OPERATOR_UUID,'''','''''')+'''' end + ',' + case when AUDIT_OPERATOR_NAME is null then 'NULL' else '''' + replace(AUDIT_OPERATOR_NAME,'''','''''')+'''' end + ',' + case when INVOICING_STYLE is null then 'NULL' else '''' + cast(replace(INVOICING_STYLE,'''','''''') as Char(1))+'''' end + ',' + case when INVOICING_DATE is null then 'NULL' else '''' + replace(INVOICING_DATE,'''','''''')+'''' end + ',' + case when BUSINESS_YEAR is null then 'NULL' else '''' + cast(replace(BUSINESS_YEAR,'''','''''') as Char(4))+'''' end + ',' + case when ORG_CD is null then 'NULL' else '''' + replace(ORG_CD,'''','''''')+'''' end + ',' + case when SEND_STATE is null then 'NULL' else '''' + cast(replace(SEND_STATE,'''','''''') as Char(1))+'''' end + ',' + case when MODIFY_TIME is null then 'NULL' else '''' + cast(MODIFY_TIME as varchar)+'''' end + ',' + case when SEND_TIME is null then 'NULL' else '''' + cast(SEND_TIME as varchar)+'''' end + ',' + case when PACKAGE_TIME is null then 'NULL' else '''' + cast(PACKAGE_TIME as varchar)+'''' end + ',' + case when DATA_STATE is null then 'NULL' else '''' + cast(replace(DATA_STATE,'''','''''') as Char(1))+'''' end + ',' + case when LAST_TIME is null then 'NULL' else '''' + cast(LAST_TIME as varchar)+'''' end + ',' + case when RESERVED is null then 'NULL' else '''' + replace(RESERVED,'''','''''')+'''' end + ',' + case when RESERVED3 is null then 'NULL' else cast(RESERVED3 as varchar) end + ',' + case when TRANSFER_DATE is null then 'NULL' else '''' + replace(TRANSFER_DATE,'''','''''')+'''' end + ',' + case when CHECK_AMOUNT_STATUS is null then 'NULL' else '''' + replace(CHECK_AMOUNT_STATUS,'''','''''')+'''' end + ',' + case when CHECK_AMOUNT_INFO_CD is null then 'NULL' else '''' + replace(CHECK_AMOUNT_INFO_CD,'''','''''')+'''' end + ',' + case when TRANSFER_INFO_CD is null then 'NULL' else '''' + replace(TRANSFER_INFO_CD,'''','''''')+'''' end + ',' + case when BANK_TRANSFER_DATE is null then 'NULL' else '''' + cast(BANK_TRANSFER_DATE as varchar)+'''' end + ',' + case when VERSION is null then 'NULL' else cast(VERSION as varchar) end + ',' + case when REVERSAL_STATUS is null then 'NULL' else '''' + replace(REVERSAL_STATUS,'''','''''')+'''' end + ',' + case when REVERSAL_INVOICE_NO is null then 'NULL' else '''' + replace(REVERSAL_INVOICE_NO,'''','''''')+'''' end + ',' + )' from yy_sg.CS_TOBACCO_BIZ_AUDIT