replacing expression with dyamically created temp table
DECLARE @UpdateColumns NVARCHAR(MAX) = replace(Stuff((Select ' ,T.['+ Convert(Varchar(100),i.reward_user_extra_type ) +'] = isnull( S.[' + Convert(Varchar(100), i.reward_user_extra_type) +']' + ' ,T.['+ Convert(Varchar(100),i.reward_user_extra_type ) +'])' From #inserted i where i.rowid = @u for xml path('')), 1, 2, ''),' ','') How do I replace the #inserted with [##' + @TmpGlobalTable + '] table in above expression?Please help me out.
Because the temp table name is a variable, you have to generate dynamic SQL to populate the column list whereas with known table name you could just execute the select statement. You need a variable to store the script which can be executed. The **sp\_executesql** does accept parameters (both in and out), so the original @UpdateColumns variable can be used for the out parameter. Here is the script which should produce desired output (replace of the carriage return is not needed in my version because I use char(10) instead of hitting Enter key which caused the carriage returns to be entitized by xml path. I still left the replace there just in case): declare @updateColumnsSql nvarchar(max); declare @updateColumns varchar(max); select @updateColumnsSql = 'select @updateColumnsOut = replace(stuff(( select '', ' + char(10) + 'T.['' + convert(varchar(100), i.reward_user_extra_type ) + ''] = isnull(S.['' + convert(varchar(100), i.reward_user_extra_type ) + ''], T.['' + convert(varchar(100), i.reward_user_extra_type ) + ''])'' from [##' + @tmpGlobalTable + '] i where RowID = ' + cast(@u as varchar) + ' for xml path('''')), 1, 3, ''''), '' '', ''''); '; exec sp_executesql @updateColumnsSql, N'@updateColumnsOut varchar(max) out', @updateColumnsOut = @updateColumns out; -- uncomment to see results --select @updateColumns; Whenever there is a need to translate the static SQL to dynamic, the first step might be to highlight the original script and replace every occurrence of the single quote with the pair of single quotes via Ctrl + H. After that the resulting string can be wrapped into single quotes. This produces the good starting point because the resulting string is already a valid script. It would be easier to opt for wrapping the updates/inserts into transaction, as discussed in the [answer to one of your earlier questions], so there would be no necessity to define a dynamically named global temp table, but this is your call. Hope this helps, Oleg :