HOW TO INSERT DATA FROM ONE TABLE TO ANOTHER TABLE

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

Answer 1

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, [e-mail]

Answer 2

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