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

declare @tableName varchar(50)set @tableName='Shop_Product'declare @colName varchar(50),@typeName varchar(30),@isnull int,@order intdeclare @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.xusertypeWHERE col.id = object_id(@tableName)open curfetch next from cur into @colName,@typeName,@isnull,@orderwhile @@FETCH_STATUS = 0begin    IF @order > 1beginSELECT @query=@query + ',',@cols=@cols + ',',@paras=@paras + ','endif @colName in ('AgentId','AgenID') begin    set @where=' WHERE '+@colName+'=''%%AgentId%%'''endSELECT @cols=@cols + @colName,@paras=@paras + '?'    IF @isnull = 0BEGINif @typeName = 'datetime'begin    set @query= @query + 'Convert(Varchar(20),'+@colName+',120) '+@colName+char(13)+char(10)endelse if @typeName = 'bit'begin    --Y or N 无法识别    set @query= @query + 'Convert(tinyint,'+@colName+') '+@colName+char(13)+char(10)endelsebegin    set @query=@query + @colName+char(13)+char(10)endENDelsebegin    if @typeName='datetime' begin    set @query= @query + 'ISNULL(Convert(Varchar(20),'+@colName+',120),'''') '+@colName+char(13)+char(10)endelse if @typeName='bit' begin    set @query= @query + 'ISNULL(Convert(tinyint,'+@colName+'),0) '+@colName+char(13)+char(10)endelse 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)endelse if @typeName='uniqueidentifier'begin    set @query= @query + 'ISNULL('+@colName+',''00000000-0000-0000-0000-000000000000'') '+@colName+char(13)+char(10)endelsebegin    set @query= @query + 'ISNULL('+@colName+','''') '+@colName+char(13)+char(10)endend    fetch next from cur into @colName,@typeName,@isnull,@orderendSELECT @query= @query+' FROM '+@tableName +' with(nolock)'+@whereclose curdeallocate curset @insert = ''--有标识列if OBJECTPROPERTY(OBJECT_ID(@tableName),'TableHasIdentity')= 1begin    set @insert = @insert+'SET IDENTITY_INSERT '+@tableName+' ON '+char(13)+char(10)endset @insert = @insert+'INSERT INTO '+@tableName+'('+@cols+')'+char(13)+char(10)set @insert = @insert+'VALUES('+@paras+')'select @query query,@insert insrt