Loading data to different tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Loading data to different tables

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2007-08-18 : 14:03:55

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?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-18 : 14:06:56

first load the master table then load the child tables.

insert into masterTable
select ....

insert into childTable1
select ....

insert into childTable2
select ....

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

jung1975
Aged Yak Warrior

503 Posts

Posted - 2007-08-18 : 15:26:29

thanks but 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?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-18 : 15:34:13

SET IDENTITY_INSERT <TableNameHere> ON | OFF

E 12°55'05.25"
N 56°04'39.16"

jung1975
Aged Yak Warrior

503 Posts

Posted - 2007-08-18 : 15:55:38

thanks,.. but my question is how I can insert the registration_key in episode table since the registration _key was generated on the fly ( identity)in the registration table?

jung1975
Aged Yak Warrior

503 Posts

Posted - 2007-08-18 : 16:03:25

insert into registration
select unique_id,gender,ethnicity, race from data_staging

go

insert into episode
select
registration.Registration_key ( how can i grap this value since these are the identity column from registration table and there are no link back to the master table?)
MCP_key,
Admission_Dt,
Discharge_Date
from data_staging

go
insert into assessment
select Registraion_Key(FK),Episode_Key(FK),Enrollment_key ( Same issue)
Reason
Disability
Income
Employment
from data_staging

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-18 : 16:26:54

use the OUTPUT statement

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

jung1975
Aged Yak Warrior

503 Posts

Posted - 2007-08-18 : 16:30:46

can you show me some examples?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-18 : 16:39:15

look in BOL:
http://technet.microsoft.com/en-us/library/ms177564.aspx

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources