Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Posts
    28

    Unanswered: how to copy detail records to another header

    Im trying to copy details from a specific header as details of a different header (eg. all sales items from invoice #10 copied as sales items of invoice #11).

    So far I have two stored procedures:
    1) sp_copyDetailsOne
    Code:
    /*Create a recordset of the desired items to be copied*/
    CREATE PROCEDURE sp_copyDetailsOne @invoiceIdFrom INT, @outCrsr CURSOR VARYING OUTPUT  AS
    SELECT itemId, itemPrice, itemDescription, itemQuantity
    FROM tblSalesItems
    WHERE (invoiceId = @invoiceIdFrom)
    OPEN @outCrsr
    2) sp_copyDetailsTwo
    Code:
    CREATE PROCEDURE sp_copyDetailsTwo @invoiceIdFrom INT, @invoiceIdTo INT 
    /*Allocate a cursor variable*/
    DECLARE @crsrVar CURSOR
    
    /*Execute sp_copyDetailsOne to instantiate @crsrVar*/
    EXEC sp_copyDetailsOne @invoiceIdFrom, @outCrsr = @crsrVar OUTPUT
    
    /*Loop through the recordset and insert a new record using the new invoiceId*/
    FETCH NEXT FROM @crsrVar
    WHILE @@FETCH_STATUS = 0
    BEGIN
    
    /*Run the insert here*/
    INSERT INTO tblSalesItems
        (invoiceId, itemId, itemPrice, itemDescription, itemQuantity)
    VALUES (@invoiceIdTo , 5, $25.00, N'Black T-Shirt', 30)
    
    /*Fetch next record from cursor*/
    FETCH NEXT FROM @crsrVar
    END
    
    CLOSE @crsrVar
    DEALLOCATE @crsrVar
    My question comes on the Insert of sp_copyDetailsTwo, as you can see the values are hard coded and I want them pulled from the cursor. However I don't know how to do this - do I need varables or can I access the cursor values directly in my VALUES clause? Or is this whole approach needing overhauled. Any advice is welcome.

    Thanks

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    is this a one time operation? If so, won't insert (...) (select ... +1) do?

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Your FETCH just displays the data accessed by the cursor. You need to FETCH...INTO a list of variables that would hold the values that you'll use in VALUES clause of your INSERT (@itemPrice, @itemDescription, @itemQuantity)
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •