1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Insert data from TableA to TableB

Discussion in 'SQL Server' started by fdtoo, Nov 6, 2007.

  1. fdtoo

    fdtoo New Member

    Joined:
    Apr 12, 2006
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    0
    I have the following fields in TableA running on SQL Server 2000:

    Dates as datetime
    Item_ID as int (Primary key)
    Item_No as int
    Qty as numeric
    Unit_Price as numeric

    TableA consist of the following info :
    ======================================
    Dates | Item_ID | Item_No | Qty | Unit_Price
    ======================================
    30/10/2007 | IT1000 | 1234 | 2 | 4
    ======================================
    30/10/2007 | IT1001 | 1235 | 2 | 6
    ======================================
    28/09/2007 | IT1002 | 1236 | 4 | 8
    ======================================
    01/11/2007 | IT1003 | 1235 | 2 | 2

    I have to assign 3 units of Item_No 1235 from TableA into TableB, with the following rules :

    assuming 3 units is a variable declared in sql stored procedure

    1) assign units from the earliest date or earliest Item_ID to Qty field in TableB
    => 2 units from Item_ID : IT1001 insert to Qty field in TableB
    => Unit_Price of 6 from Item_ID : IT1001 insert to Unit_Price field in TableB

    2) assign the balance of 1 unit from the next earliest date or earliest Item_ID to Qty field in TableB
    => 1 unit from Item_ID : IT1003 insert to Qty field in TableB
    => Unit_Price of 2 from Item_ID : IT1003 insert to Unit_Price field in TableB

    3) Update Qty field in TableA after inserting the above quantity for Item_No 1235 into TableB

    Note:sql function should be intelligent to assign the units required base on earliest date or
    earliest Item_ID automatically. If units required is insufficient, it should be able to loop
    for the next available units, if the units to assign is greater than the units available in TableA,
    an error message should be printed, telling the user, units to assign is insufficient. If Item_No to be
    assigned does not exist in TableA, an error message should be printed, telling the user, item not exist.

    INSERT Result in TableB should consist the following info :

    ======================================
    Dates | Item_ID | Item_No | Qty | Unit_Price
    ======================================
    30/10/2007 | IT1001 | 1235 | 2 | 6
    ======================================
    01/11/2007 | IT1003 | 1235 | 1 | 2

    UPDATE Result in TableA should consist the following info :

    ======================================
    Dates | Item_ID | Item_No | Qty | Unit_Price
    ======================================
    30/10/2007 | IT1000 | 1234 | 2 | 4
    ======================================
    30/10/2007 | IT1001 | 1235 | 0 | 6
    ======================================
    28/09/2007 | IT1002 | 1236 | 4 | 8
    ======================================
    01/11/2007 | IT1003 | 1235 | 1 | 2

    Can a sql function be created for the above action? Thanks guys!
     
  2. sql-programs

    sql-programs New Member

    Joined:
    Oct 21, 2009
    Messages:
    14
    Likes Received:
    2
    Trophy Points:
    0
    Occupation:
    Software Developer
    Home Page:
    Use the following scripts,

    Code:
    declare @Qty as int
    declare @ItemID as varchar(10)
    declare @Units as int
    declare @Item_No as int
    set @Units=3
    set @Item_No=1235
    declare @TotQty as int
    declare @iQty as int
    
    set @TotQty=(select sum(Qty) from TableA where Item_No=@Item_No)
    
    if  @TotQty<=@Units 
    begin
        insert into TableB select * from TableA where Item_No=@Item_No
        update TableA set Qty=0 where Item_No=@Item_No
    end
    else
    begin
        set @TotQty=0
        DECLARE Cur CURSOR FOR
          select Qty,Item_ID from TableA where Item_No=@Item_No order by Dates
        OPEN Cur
        FETCH NEXT FROM Cur INTO @Qty,@ItemID
        WHILE @@FETCH_STATUS = 0
        BEGIN
    
            set @TotQty=@TotQty+@Qty
    
            if  @TotQty<=@Units 
            begin
                insert into TableB select * from TableA where Item_ID=@ItemID                                            
                update TableA set Qty=0 where Item_ID=@ItemID    
                
            end
            else
            begin
                set @iQty=@TotQty-@Units    
                if  @TotQty<=@Units 
                begin
                    insert into TableB select [Dates],[Item_ID],
                        [Item_No],@Units-(@TotQty-@Qty),[Unit_Price] from TableA where Item_ID=@ItemID                                                            
                    update TableA set Qty=@iQty where Item_ID=@ItemID    
                end    
            end
    
        FETCH NEXT FROM Cur INTO @Qty,@ItemID
        END
        CLOSE Cur
        DEALLOCATE Cur
    end

    Note: You referred the Item_ID as int , but values used for that columns as IT1000,IT1001,IT1002,IT1003
    So I declared as varchar(10) type.
     

Share This Page