Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Database Design » Relational Theory » How to Add Parent Table Record Id in Child...
Add to briefcase

How to Add Parent Table Record Id in Child Table Using Foreign Key Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Gopal Singh
Posted Thursday, April 26, 2012 4:49 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 26, 2012 7:07 PM
Points: 41, Visits: 113
Hello,
I have one parent & child table and i have make relation ship using a foreign key. Primary key in parent table is forein key in child table.

So want when i enter records in primary table, the id of each records automatically goes in to child table.

how can i do this?
waiting your reply....

Regards,

Gopal Singh
Post #1291175
opc.three
Posted Thursday, April 26, 2012 5:23 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:26 PM
Points: 6,696, Visits: 11,715
Gopal Singh (4/26/2012)
Hello,
I have one parent & child table and i have make relation ship using a foreign key. Primary key in parent table is forein key in child table.

So want when i enter records in primary table, the id of each records automatically goes in to child table.

how can i do this?
waiting your reply....

Regards,

Gopal Singh

What is inserting the data into the Parent table? If a stored procedure, you can add the additional insert into the Child there.

If it is a direct table insert into the Parent then you can achieve the desired result by implementing an AFTER INSERT TRIGGER on the Parent table that will insert rows into the Child after rows are inserted into the Parent.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1291185
Grant Fritchey
Posted Friday, April 27, 2012 5:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:19 PM
Points: 13,371, Visits: 25,144
I'm not so sure I'd use a trigger. Those things are notorious performance and maintenance headaches.

What about using the OUTPUT clause. You can output the IDs generated from an INSERT statement into a temp table or table variable and then use those values for the INSERT into the child table. That's the method I'll use when I need to do something like this.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1291399
opc.three
Posted Friday, April 27, 2012 7:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:26 PM
Points: 6,696, Visits: 11,715
Grant Fritchey (4/27/2012)
I'm not so sure I'd use a trigger. Those things are notorious performance and maintenance headaches.

What about using the OUTPUT clause. You can output the IDs generated from an INSERT statement into a temp table or table variable and then use those values for the INSERT into the child table. That's the method I'll use when I need to do something like this.

I agree with Grant here, if you have control over the process doing the insert, such as within a stored procedure.

A trigger, my second thought, would be your fallback if you do not have control over the process doing the insert, such as when someone has direct access to the tables and can issue inserts to the parent and may not follow a documented procedure to also populate the child table.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1291497
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.