Post reply

Import Child Records using Stored Procedure or Trigger...need start/Help

  • July 14, 2008 at 5:22 pm

    #195274

    have the following stored procedure:

    USE [PeachTestImport]

    GO

    /****** Object: StoredProcedure [dbo].[ImportOrders] Script Date: 07/14/2008 11:45:55 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER PROCEDURE [dbo].[ImportOrders]

    AS

    BEGIN

    SET NOCOUNT ON;

    INSERT WorkOrders (SONo,ProductDesc,QtyToRun, AssemblyRecordNo)

    SELECT Reference, RowDescription,QtyOrdered, ItemRecordNumber

    FROM View_WorkOrders

    END

    This SP imports sales order records & creates Work Orders from them. My questions is this:

    I need to import a list of bill of materials into another table (BOM) after each work order is imported. The FK link would be AssemblyRecordNo for the import, but I would need to grab the SONo from WorkOrders and insert that into BOM child fields.

    I.e. if I import 1 work order (WO # 1234) it may have 3 BOM records associated with the AssemblyRecordNo (34) so the table would look like this for Work Orders:

    WONO SONo ItemID AssemblyRecordNO

    1234 4300 DELREF 34

    And the child BOM Table:

    SONO BOMID ComponentNo AssemblyRecordNo

    4300 10 1 34

    4300 11 2 34

    4300 12 3 34

    Thanks!

  • July 15, 2008 at 6:08 am

    #842997

    Take a look at the OUTPUT clause. You can use that to capture inserted data, including identity values, into a temporary table for use in later processing.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • July 15, 2008 at 6:48 am

    #843042

    Thanks!

    V

Viewing 3 posts - 1 through 2 (of 2 total)

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