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 Add Parent Table Record Id in Child Table Using Foreign Key Expand / Collapse
Author
Message
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
Posted Thursday, April 26, 2012 5:23 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:27 PM
Points: 7,107, Visits: 12,657
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
Post #1291185
Posted Friday, April 27, 2012 5:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 6:38 AM
Points: 13,755, Visits: 28,147
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
Posted Friday, April 27, 2012 7:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:27 PM
Points: 7,107, Visits: 12,657
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
Post #1291497
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse