Post reply

Insert into Master and Detail Table using Stored Procedure

  • July 25, 2012 at 1:59 am

    #261526

    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

  • July 25, 2012 at 2:25 am

    #1517215

    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

  • July 25, 2012 at 2:31 am

    #1517218

    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

  • July 25, 2012 at 2:44 am

    #1517226

    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/

  • July 25, 2012 at 3:11 am

    #1517239

    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

  • July 25, 2012 at 3:54 am

    #1517256

    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...

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

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