Sean Lange (7/22/2013)

---

I agree with Steve that if this information is potentially sensitive then the system was designed poorly. This type of information should NOT be populated through a number of tables. Unfortunately that is what you have to deal with today.

I also agree with Steve that this is a potential issue for somebody new to the sql universe.

If however all you really need to is to mask the actual values in a test environment this really is not that difficult. I put together an example of how you could do something like this.

Please note that is only an example. Your real situation is likely to be a bit more complicated than this.

if OBJECT_ID('tempdb..#ClientInfo') is not null

drop table #ClientInfo

if OBJECT_ID('tempdb..#ClientAccount') is not null

drop table #ClientAccount

create table #ClientInfo

(

ExistingID int,

NewValue int

)

--This will generate 500 random integer values that will serve as "existing ID's"

--You would not do this in your system, this is just generating some data that is

--used to represent the table of data that you already have.

insert #ClientInfo(ExistingID)

select top 500 * from

(

select distinct ABS(CHECKSUM(NEWID())) % 10000 + 400 as NewVal

from sys.all_columns

) x

create table #ClientAccount

(

ExistingID int, --this is the column used in joins currently if I understand correctly

NewValue int

)

insert #ClientAccount (ExistingID)

select ExistingID

from #ClientInfo

--Now we have two tables that both have values that we want to "mask"

select *

from #ClientInfo

order by ExistingID

select *

from #ClientAccount

order by ExistingID

/*

Now that we have those two tables how can we go about mixing up the values?

Let's start looking at NewValue in each table to hold the new values.

In the real scenario we do not need these extra columns, I am including them for a visual reference and testing confirmation.

*/

update #ClientInfo

set NewValue = ExistingID

update #ClientAccount

set NewValue = ExistingID

select * from #ClientInfo --This will demonstrate that the values are the same

select * from #ClientAccount

--This is where you would start. Everything above here is just setting up the example.

update #ClientInfo

set ExistingID = floor(((ExistingID * 400) / 123.456789) * 100)

update #ClientAccount

set ExistingID = floor(((ExistingID * 400) / 123.456789) * 100)

--Cool we twisted the numbers around. But, did it work? Can we still join on our numbers?

select *

from #ClientAccount ca

join #ClientInfo ci on ca.ExistingID = ci.ExistingID