Kettle Sql Server插入语句和执行语句生成方法

declare @tableName varchar(50)
set @tableName='Shop_Product'

declare @colName varchar(50),@typeName varchar(30),@isnull int,@order int
declare @query varchar(4000),@where varchar(100),@cols varchar(4000),@paras varchar(4000)

declare @insert varchar(4000)

select @query = 'SELECT '+char(13)+char(10),@cols = '',@paras='',@where=''

declare cur cursor for 
SELECT col.name as ColName,st.name typeName,col.isnullable,col.colid 
FROM syscolumns col join systypes st on col.xusertype = st.xusertype
WHERE col.id = object_id(@tableName)
open cur
fetch next from cur into @colName,@typeName,@isnull,@order
while @@FETCH_STATUS = 0
begin
    IF @order > 1
	begin
		SELECT @query=@query + ',',@cols=@cols + ',',@paras=@paras + ','
	end

	if @colName in ('AgentId','AgenID') 
	begin
	    set @where=' WHERE '+@colName+'=''%%AgentId%%'''
	end

	SELECT @cols=@cols + @colName,@paras=@paras + '?'
    IF @isnull = 0
	BEGIN
		if @typeName = 'datetime'
		begin
		    set @query= @query + 'Convert(Varchar(20),'+@colName+',120) '+@colName+char(13)+char(10)
		end
		else if @typeName = 'bit'
		begin
		    --Y or N 无法识别
		    set @query= @query + 'Convert(tinyint,'+@colName+') '+@colName+char(13)+char(10)
		end
		else
		begin
		    set @query=@query + @colName+char(13)+char(10)
		end
	END
	else
	begin
	    if @typeName='datetime' 
		begin
		    set @query= @query + 'ISNULL(Convert(Varchar(20),'+@colName+',120),'''') '+@colName+char(13)+char(10)
		end
		else if @typeName='bit' 
		begin
		    set @query= @query + 'ISNULL(Convert(tinyint,'+@colName+'),0) '+@colName+char(13)+char(10)
		end
		else if @typeName in ('int','tinyint','decimal','bigint','real','float','money','numeric','smallint','smallint')
		begin
		    set @query= @query + 'ISNULL('+@colName+',0) '+@colName+char(13)+char(10)
		end
		else if @typeName='uniqueidentifier'
		begin
		    set @query= @query + 'ISNULL('+@colName+',''00000000-0000-0000-0000-000000000000'') '+@colName+char(13)+char(10)
		end
		else
		begin
		    set @query= @query + 'ISNULL('+@colName+','''') '+@colName+char(13)+char(10)
		end
	end
    fetch next from cur into @colName,@typeName,@isnull,@order
end

SELECT @query= @query+' FROM '+@tableName +' with(nolock)'+@where
close cur
deallocate cur

set @insert = ''
--有标识列
if OBJECTPROPERTY(OBJECT_ID(@tableName),'TableHasIdentity')= 1
begin
    set @insert = @insert+'SET IDENTITY_INSERT '+@tableName+' ON '+char(13)+char(10)
end

set @insert = @insert+'INSERT INTO '+@tableName+'('+@cols+')'+char(13)+char(10)
set @insert = @insert+'VALUES('+@paras+')'

select @query query,@insert insrt