Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to switch ID number to new values (but maintaining duplicates for new ones) Expand / Collapse
Author
Message
Posted Thursday, April 04, 2013 12:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 15, 2013 11:45 AM
Points: 4, Visits: 4
Hello!!!

I need a database from an Insurance company, but they don't want to give it because it has a column with the ID number of each patient and their medical history.

They will give it to me only if I can find a 'script' that allows them to switch that ID number, but I need to 'remember' same ID across all the dase...

For example,

If I have (in a column) the ID 12345 and you 54321, y must generate a new number for each one. It can be 1 for me and 2 for you; but each time it reads 12345 it has to change to 1 exclusively.
In conlution, it has to identify each person with a new ID number.

Thanks !!!
Post #1438955
Posted Thursday, April 04, 2013 12:10 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:39 PM
Points: 12,749, Visits: 31,114
would doing something like selecting a newid() help?
for example, have them do something like this:
SELECT 
NEWID() AS RefID,
OtherColumnsButNotTheRealID
Into TableForExport
From tblPatients
--OR
SELECT
identity(int,1,) AS RefID,
OtherColumnsButNotTheRealID
Into TableForExport
From tblPatients

then have them send you the TableforExport, which would not have the sensitive column.

they could do the same for patient names as well, if they needed to...just select a number instead of a patient name.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1438959
Posted Thursday, April 04, 2013 3:56 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 04, 2014 2:44 PM
Points: 151, Visits: 378
or if you are lazy to list the column name do this.

 
SELECT NEWID() AS [NewID], * INTO NewTable
FROM ExistingTable;
ALT_R TABLE NewTable DR_P COLUMN OldID



*fill in the blank
Post #1439037
Posted Friday, April 05, 2013 3:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:40 AM
Points: 6,777, Visits: 12,883
lda17_04 (4/4/2013)
Hello!!!

I need a database from an Insurance company, but they don't want to give it because it has a column with the ID number of each patient and their medical history.

They will give it to me only if I can find a 'script' that allows them to switch that ID number, but I need to 'remember' same ID across all the dase...

For example,

If I have (in a column) the ID 12345 and you 54321, y must generate a new number for each one. It can be 1 for me and 2 for you; but each time it reads 12345 it has to change to 1 exclusively.
In conlution, it has to identify each person with a new ID number.

Thanks !!!


It's usually the responsibility of the supplier to obfuscate sensitive data - they should be posting the question on ssc, not you!
Something like this:

-- sample data 
DROP TABLE #SensitiveData;
SELECT *
INTO #SensitiveData
FROM (SELECT PatientID = 1, SafeStuff = '1' UNION ALL SELECT 2, '2' UNION ALL SELECT 3, '3' UNION ALL SELECT 1, '1') d;

-- generate the new keys
DROP TABLE #ObfuscatedKeys;
WITH DISTINCTPatientID AS (SELECT DISTINCT PatientID FROM #SensitiveData)
SELECT SensitiveOldKey = PatientID, SafeNewKey = NEWID()
INTO #ObfuscatedKeys
FROM DISTINCTPatientID;

-- switch keys for export
SELECT o.SafeNewKey, s.SafeStuff
FROM #SensitiveData s
INNER JOIN #ObfuscatedKeys o ON o.SensitiveOldKey = s.PatientID;



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1439135
Posted Monday, April 15, 2013 8:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 15, 2013 11:45 AM
Points: 4, Visits: 4
Thank you very much for the assistance!

Yeah.... welll.. tell them that haha... Like always, if you want something you have to searched on your own.

I will send them these suggestions, I hope they work fine.

Thanks a lot !!!
Any other idea please let me know.

Post #1442336
Posted Monday, April 15, 2013 11:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 15, 2013 11:45 AM
Points: 4, Visits: 4
Post #1442429
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse