Inserting records ( from one table ) to multiple tables

Question

Inserting records ( from one table ) to multiple tables

kcj

Mr or Mrs. 500

Points: 540
More actions
August 18, 2007 at 1:34 pm

#125281

I have a source table like below
Data_Staging:
Unique_id
Gender
Ethnicity
Race
MCP_key
Admission_Dt
Discharge_Date
Enrollment_key
Reason
Disability
Income
Employment
I need to load the data from this table to three different tables all have foreign key relationship
Registration Table:
Registration_key ( Indetity) -PK
Unique_id
Gender
Ethnicity
Race
Episode:
Episode_Key(Identity)- PK
Registration_key (FK)
MCP_key
Admission_Dt
Discharge_Date
Assessment Table:
Assessment_Key(Identity) – PK
Registraion_Key(FK)
Episode_Key(FK)
Enrollment_key
Reason
Disability
Income
Employment
How can I insert the records to three tables and keep the foreign key relationship?
I would like to use a set based insert statement ( no coursor)..
Can you show me some examples?
what about the foreign keys ( registration key and episode key) in Episode and assessment table?
these are the identity columns comes from registration and episode table.. how can i handle these keys during the child table insertion?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Please post some sample data and the exact required result after the transfer has been made. That way we can all be sure of the problems you fear to encounter.

kcj Mr or Mrs. 500 Points: 540 More actions · Answer 2

Data_Staging:

Unique_id Gender Ethnicity RACE MCP_Key Admission_DT Discharge_date Enrollment_key Reason Disbility Income Employment

100A1 M A 3 1010 01/4/1999 11/3/2006 163278 R Y 47389 UIN90

Here is the tables that I need to insert the records

Registration:

REgistration_key(Identity) unique_id GEnder Ethnicity Race

1000 100A1 M A 3

Episode

Episode_key(Identity) Registration_key(FK) ADmission_DT Discharge_Date

1 1000 01/4/1999 11/3/2006

Assessment:

Assessment_key ( identity) Registration_key(FK) Episode_key(FK) Enrollment_key Reasoin Disability Income Employment

10001 1000 1 163278 R Y 47389 UIN90

Mohit Nayyar Ten Centuries Points: 1008 More actions · Answer 3

You can achieve the same using standard T-SQL with these steps, but I would like to inform you that I am making some really BIG assumptions, so please go through these scripts carefully and then use them in your system. And start this with one unique_id (100A1) in data_staging table.

Insert into registration (unique_id, gender, Ethnicity, Race)

Select unique_id, gender, Ethnicity, Race from data_staging

Now after the above step we have data with registration_key in registration table.

Insert into episode (registration_key, admission_dt, discharge_date)

Select r.registration_key, s.admission_dt, s.discharge_date

from data_staging s

inner join registration r on (r.unique_id = s.unique_id)

Now we have data in episode table as well, along with Episode_key.

Insert into Assessment (registration_key, episode_key, enrollment_key, reason, [Disability Income], Employment)

Select e.registration_key, e.episode_key, s.enrollment_key, s.reason, s.[Disability Income], s.Employment

from data_staging s

inner join registration r on (r.unique_id = s.unique_id)

inner join episode e on (r.registration_key = e.registration_key)

That’s it. We are done here.

Thanks
Mohit Nayyar
http://mohitnayyar.blogspot.com/
"If I am destined to fail, then I do have a purpose in my life, To fail my destiny"

kcj Mr or Mrs. 500 Points: 540 More actions · Answer 4

kcj

Mr or Mrs. 500

Points: 540

August 19, 2007 at 10:37 am

#727871

thanks! I will try it and let you know