Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm creating a data warehouse for a healthcare company. They have separate databases for different hospitals which contain tables on patients,their insurance,etc and PK is unique only within one hospital DB. When merged, I'm supposed to create a Master Patient Table, a Master Insurance Company table,etc that combines duplicate data into one record(eg. by comparing name and SSN fields for patients).

Any suggestions on how to do this merge, and create correct FK references in the new tables? A record in the Patient table needs to have a correct reference to an insurance company in the Insurance table. How do I preserve this?

I'll be using Oracle for both the source and destination databases.

share|improve this question

closed as too broad by Mark Storey-Smith, Phil, RolandoMySQLDBA, Max Vernon, Kin Jul 22 at 18:43

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the question.

2 Answers 2

You would need to something called data cleansing. There are tools within which such logic can be written for example IDQ or IBM QualityStage if not then you would need to write Stored Procedures while doing the ETL to de-duplicate/clean up the data and then load the final list into DWH.

share|improve this answer

NB:- I am giving the sql syntax of MS SQL Server(i am only familiar with).I guess you can develop from it.

First, you have to select the data using sql. Usually UNION returns only distinct raw

SELECT LastName, FirstName, Address, City, State, Zip
FROM HospitalOne
UNION
SELECT LastName, FirstName, Address, City, State, Zip
FROM HospitalTwo
ORDER BY LastName, FirstName;

You may save it to an sql view say HospitalFullView

Then insert the data into table, say AllHospitals is the new table and HospitalFullView is the above results of joined datas

insert INTO AllHospitals(LastName, FirstName, Address, City, State, Zip)
SELECT LastName, FirstName, Address, City, State, Zip from HospitalFullView

I hope this helps.

You may also try this (this also MSSQL but i believes this might help)

https://www.simple-talk.com/sql/t-sql-programming/removing-duplicates-from-a-table-in-sql-server/

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.