data shake up

[fatherjack]

i am starting work on developing an HR system and have been advised that i cannot have a copy of the live HR data for obvious reasons. I am reluctant to use made up data as it wont have the correct history or peculiarities of the live dataset.

is there a way or randomising the data so that noone in the database is recognisable? i can see that certain fields could be adjusted by adding random numbers of days, weeks or months to all date fields, multiplying the currency fields by a random %age etc..

does anyone have any ideas about how it could be achieved? I would like to be able to put the process into a DTS or adhox job so that i can take a new set of data as and when the test data becomes out of date.

tia

[MAK]

You cannot get live data from the HR because it has sensitive data like SSN, Salary, benefits package, bonus package and history etc.... of the entire firm.

but nothing can be hidden from Production DBA unless the application itself encrypts the data and stores encrpted data.

Ask your production DBA to export the tables to files either encrypting the sensitive data or by giving NULL as values in those columns.

[fatherjack]

i am the production DBA, HR have Board backing to prevent me from using a copy of live data in development and testing. I know i can see the data in the live db but i am not allowed to see it during testing - just dont ask!!

I need to run the export and do the 'mix up' on the way. Nulls wont do as we are developing the application and the data needs to be there, just not the same as live

[MAK]

CREATE TABLE MYTABLE (IDX INT, NAME VARCHAR(100))
Insert into mytable select NULL,'a'
Insert into mytable select NULL,'b'
Insert into mytable select NULL,'c'
Insert into mytable select NULL,'d'
Insert into mytable select NULL,'e'


update mytable set idx =CONVERT(INT,ASCII(LEFT(NEWID(),1)))

select * from mytable

--Result
IDX NAME
----------- -
70 a
56 b
69 c
50 d
53 e

[MAK]

update mytable set idx =CONVERT(INT,ASCII(LEFT(NEWID(),1))*ASCII(LEFT(NEW ID(),1))*ASCII(LEFT(NEWID(),1)))

select * from mytable

--Result
IDX NAME
----------- --
159885 a
199920 b
165750 c
239580 d
251940 e