T-SQL write modified column data to new column

Question

I have a temporary table that I want to perform some data manipulation on, and then write back to the originating table. However, as a first step I would like to compare the original data side by side with the altered data.

Given the query below, how can I write to the modified data to the a new column in the temporary table so that I can compare side by side? I'm using SQL Server 2008.

UPDATE #TempCustomControls
SET ConfigValue =
CASE 
    WHEN ConfigValue like '%GetPersonProperty%$PersonID$%'
    THEN 'PersonLogic.GetPersonProperty($PersonID$, ' + RIGHT(ConfigValue, charindex(',', REVERSE(ConfigValue))-1) + ')'

    WHEN ConfigValue like '%GetProfileProperty%$PersonID$%'
    THEN 'PersonLogic.GetPersonProperty($PersonID$, ' + RIGHT(ConfigValue, charindex(',', REVERSE(ConfigValue))-1) + ')'

    WHEN ConfigValue like '%GetProfileProperty%GetJobCreatedByPerson%$JobID$%'
    THEN 'PersonLogic.GetPersonPropertyFromJobID($JobId$, ' + RIGHT(ConfigValue, charindex(',', REVERSE(ConfigValue))-1) + ')'
ELSE
    ConfigValue 
END

Pரதீப் · Accepted Answer · 2016-10-25 15:35:43Z

1

Add a new column in temp table and update the new column instead of ConfigValue.

Alter table #TempCustomControls add new_value varchar(100)

UPDATE #TempCustomControls
SET new_value =
CASE 
    WHEN ConfigValue like '%GetPersonProperty%$PersonID$%'
    THEN 'PersonLogic.GetPersonProperty($PersonID$, ' + RIGHT(ConfigValue, charindex(',', REVERSE(ConfigValue))-1) + ')'

    WHEN ConfigValue like '%GetProfileProperty%$PersonID$%'
    THEN 'PersonLogic.GetPersonProperty($PersonID$, ' + RIGHT(ConfigValue, charindex(',', REVERSE(ConfigValue))-1) + ')'

    WHEN ConfigValue like '%GetProfileProperty%GetJobCreatedByPerson%$JobID$%'
    THEN 'PersonLogic.GetPersonPropertyFromJobID($JobId$, ' + RIGHT(ConfigValue, charindex(',', REVERSE(ConfigValue))-1) + ')'
ELSE
    ConfigValue 
END

To check select both the columns

Select ConfigValue,new_value 
From #TempCustomControls

answered Oct 25, 2016 at 15:35

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

PixelPaul Over a year ago

Perfect! Thanks for the quick reply.

Collectives™ on Stack Overflow

T-SQL write modified column data to new column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related