Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Insert into Master and Detail Table using Stored Procedure


Insert into Master and Detail Table using Stored Procedure

Author
Message
sankar.nandakumaran
sankar.nandakumaran
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 39
Hi....

I am using SQL Server 2005 in my project. I want to know how to insert data to master as well as detail table simultaneously using stored procedure.

In my scenerio am using EmployeeMaster table and EmployeeDetail Table.
The empcode is nvarchar(300) which is primary key of EmployeeMaster table, i want pass this empcode to EmployeeDetail table after saving the EmployeeMaster.

The values inserted in EmployeeDetail table is from a grid. Here we want to note that if there is any error in inserting the detail table both tables should not be saved.

Kindly help me regarding this scenerio....

Thanks and Regards

Sankar
laurie-789651
laurie-789651
SSC-Addicted
SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)

Group: General Forum Members
Points: 424 Visits: 1271
It would be something along these lines:


DECLARE @MyTableVar TABLE
(
empcode nvarchar(300)
);

BEGIN TRANSACTION

BEGIN TRY

-- Update master table
INSERT INTO EmployeeMaster.....
OUTPUT INSERTED.empcode INTO @MyTableVar

-- Update child table
INSERT INTO EmployeeDetail (empcode, other fields...)
SELECT empcode
FROM @MyTableVar
INNER JOIN .... other tables as required

-- Commit the transaction if successful
COMMIT

END TRY

BEGIN CATCH

-- Rollback transaction if unsuccessful
ROLLBACK

END CATCH


sankar.nandakumaran
sankar.nandakumaran
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 39
Hi...

Thanks for the code , but here employee master table should contain only one row and emplioyee detail table can contain n rows. we have chech whether empcode is unique also .

Thanks and Regards

Sankar
Kingston Dhasian
Kingston Dhasian
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3001 Visits: 4995
sankar.nandakumaran (7/25/2012)
Hi...

Thanks for the code , but here employee master table should contain only one row and emplioyee detail table can contain n rows. we have chech whether empcode is unique also .

Thanks and Regards

Sankar


You can add any number of rows in the Employee Detail table if you don't have any UNIQUE constraints the empcode column

Since, you said empcode is a PRIMARY KEY in the Employee Master table, the uniqueness check will be performed automatically if you define it as a PRIMARY KEY.


Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
sankar.nandakumaran
sankar.nandakumaran
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 39
Hi.....

But my problem is how to insert n rows to detail table. i am using sql server 2005 express edition so we cannot create table valued parameters. is there any other solution other than these.

Thanks and Regards

Sankar
laurie-789651
laurie-789651
SSC-Addicted
SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)

Group: General Forum Members
Points: 424 Visits: 1271
It depends what front end you're using. You might use ADO.NET with a transaction, or you could set up individual SQL stored procedures to insert the master & child records separately, with the transaction & error handing controlled by the application - in C# for instance. That's a bit outside this forum though...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search