locked
HOW TO INSERT DATA FROM ONE TABLE TO ANOTHER TABLE RRS feed

  • Question

  • how to insert data to a specific column against each id

    I have two table like partiesbalance & partyledger. partyledger has already some data now i want to just want to insert currentbalance column in partyledger table where both tables are same id

    suppose

    partyid          partyName       previousbal        currentbalance

    1                    abc                  100

    2                    cde                  200

    3                   ddf                    200

    now i want to insert just in currentcolumn where both table have same partyid

    following is my query but could not provide me expected result

    INSERT INTO PartyLedger ( CurrentBalance )
    SELECT PartiesBalances.CurrentBalance
    FROM PartiesBalances
    WHERE PartiesBalances.Partyid=PartyLedger.PartyID
    GROUP BY PartiesBalances.Partyid


    • Edited by haqayyum Saturday, February 9, 2013 10:25 AM
    Saturday, February 9, 2013 10:24 AM

Answers

  • If I understand this correctly, you need

    UPDATE PartyLedger
    SET    CurrentBalance = (SELECT PartiesBalances.CurrentBalance
                             FROM   PartiesBalances
                             WHERE PartiesBalances.Partyid=PartyLedger.PartyID)
    WHERE  EXISTS (SELECT *
                   FROM PartiesBalances
                   WHERE PartiesBalances.Partyid=PartyLedger.PartyID)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Sarat (SS) Saturday, February 9, 2013 2:54 PM
    • Marked as answer by Iric Wen Monday, February 18, 2013 7:20 AM
    Saturday, February 9, 2013 11:03 AM
  • how to insert data to a specific column against each id

    I have two table like partiesbalance & partyledger. partyledger has already some data now i want to just want to insert currentbalance column in partyledger table where both tables are same id

    suppose

    partyid          partyName       previousbal        currentbalance

    1                    abc                  100

    2                    cde                  200

    3                   ddf                    200

    now i want to insert just in currentcolumn where both table have same partyid

    following is my query but could not provide me expected result

    INSERT INTO PartyLedger ( CurrentBalance )
    SELECT PartiesBalances.CurrentBalance
    FROM PartiesBalances
    WHERE PartiesBalances.Partyid=PartyLedger.PartyID
    GROUP BY PartiesBalances.Partyid


    Try to use Update instead, like

    Update a
    set a.CurrentBalance=b.CurrentBalance
    FROM PartyLedger a inner join PartiesBalances b
    on a.Partyid=b.PartyID;


    Many Thanks & Best Regards, Hua Min



    • Edited by Jackson_1990 Saturday, February 9, 2013 12:19 PM
    • Proposed as answer by Sarat (SS) Saturday, February 9, 2013 2:54 PM
    • Marked as answer by Iric Wen Monday, February 18, 2013 7:20 AM
    Saturday, February 9, 2013 12:18 PM

All replies

  • If I understand this correctly, you need

    UPDATE PartyLedger
    SET    CurrentBalance = (SELECT PartiesBalances.CurrentBalance
                             FROM   PartiesBalances
                             WHERE PartiesBalances.Partyid=PartyLedger.PartyID)
    WHERE  EXISTS (SELECT *
                   FROM PartiesBalances
                   WHERE PartiesBalances.Partyid=PartyLedger.PartyID)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Sarat (SS) Saturday, February 9, 2013 2:54 PM
    • Marked as answer by Iric Wen Monday, February 18, 2013 7:20 AM
    Saturday, February 9, 2013 11:03 AM
  • how to insert data to a specific column against each id

    I have two table like partiesbalance & partyledger. partyledger has already some data now i want to just want to insert currentbalance column in partyledger table where both tables are same id

    suppose

    partyid          partyName       previousbal        currentbalance

    1                    abc                  100

    2                    cde                  200

    3                   ddf                    200

    now i want to insert just in currentcolumn where both table have same partyid

    following is my query but could not provide me expected result

    INSERT INTO PartyLedger ( CurrentBalance )
    SELECT PartiesBalances.CurrentBalance
    FROM PartiesBalances
    WHERE PartiesBalances.Partyid=PartyLedger.PartyID
    GROUP BY PartiesBalances.Partyid


    Try to use Update instead, like

    Update a
    set a.CurrentBalance=b.CurrentBalance
    FROM PartyLedger a inner join PartiesBalances b
    on a.Partyid=b.PartyID;


    Many Thanks & Best Regards, Hua Min



    • Edited by Jackson_1990 Saturday, February 9, 2013 12:19 PM
    • Proposed as answer by Sarat (SS) Saturday, February 9, 2013 2:54 PM
    • Marked as answer by Iric Wen Monday, February 18, 2013 7:20 AM
    Saturday, February 9, 2013 12:18 PM