Sean Lange (2/27/2013)
This should cover the Updates and Deletes. I don't understand how the insert logic is supposed to work.
declare @SQL varchar(max) = ''
--First is the updates
select @SQL = stuff ((
select
'Update dbo.' +
case ERRORMESSAGE
when 'ED ERROR' then 'Education'
when 'O14 ERROR' then 'FOURTEEN_ALL'
when 'HL ERROR' then 'HH_LISTING'
when 'HL ERROR' then 'HH_ALL'
end
+ ' set ' + Columns_To_Fix + ' = ''' + Corrected_Value
+ ''' where zBarcode = ' + cast(zBarcode as varchar(20))
+ ' and PERSON_NUMBER = ' + cast(Person_No as varchar(20)) + ';' as MyVal
from test
where Command = 'UPDATE'
for XML PATH(''), type
).value('.','varchar(max)'), 1, 0, '')
--Now we append the deletes
select @SQL = @SQL + stuff ((
select
'DELETE dbo.' +
case ERRORMESSAGE
when 'ED ERROR' then 'Education'
when 'O14 ERROR' then 'FOURTEEN_ALL'
when 'HL ERROR' then 'HH_LISTING'
when 'HL ERROR' then 'HH_ALL'
end
+ ' where zBarcode = ' + cast(zBarcode as varchar(20))
+ ' and PERSON_NUMBER = ' + cast(Person_No as varchar(20)) + ';' as MyVal
from test
where Command = 'UPDATE'
for XML PATH(''), type
).value('.','varchar(max)'), 1, 0, '')
select @SQL
--exec sp_execute @SQL
So the insert logic is to insert a new row with only certain columns that are defined in the test table? So it will only insert a single column plus zBarcode and PERSON_NUMBER? Let me know if you can't figure it out and I can lend a hand.
I know I said it before but you really would gain a lot of benefit from normalization on your tables.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/