How can I update the data in a base SQL table based on data in another table?

Question

I have a table that I use to store data. The table is populated when a user clicks save on a grid that is on my client system. Here I made a very simple example. The table is called TableA. The client screen shows five rows and so when the user clicks save it is translated into five inserts:

CREATE TABLE [dbo].[TableA]
(
    [IdA]       INT            IDENTITY (1, 1) NOT NULL,
    [valueA] CHAR(10) NOT NULL
)
INSERT INTO TableA VALUES (1, 'one')
INSERT INTO TableA VALUES (2, 'two')
INSERT INTO TableA VALUES (3, 'three')
INSERT INTO TableA VALUES (4, 'four')
INSERT INTO TableA VALUES (5, 'five')
GO

Now my user on the client changes the data in the grid and I read the latest data into a temp table. Here I simulate that with TableB

CREATE TABLE [dbo].[TableB]
(
    [IdB]       INT            IDENTITY (1, 1) NOT NULL,
    [valueB] CHAR(10) NOT NULL
)
INSERT INTO TableB VALUES (1, 'one')
INSERT INTO TableB VALUES (3, 'newThree')
INSERT INTO TableB VALUES (4, 'newFour')
INSERT INTO TableB VALUES (5, 'five')
GO

Can someone suggest how I can use the new data in TableB to update the rows in TableA. What I would need to do in this case would be to delete the row with an ID of 2 and update the rows with IDs of 4 and 5. Each time it could be different. For example the user may add rows and so I would need to add a new row to TableA. Note that all this happens inside a stored procedure. What I really need is some advice on different ways I could join TableA and TableB to somehow create an insert, a delete and an update to TableA to get it to the latest state.

Note for this particular example what I need to end up with is:

[The `UPDATE` portion has been asked and answered many, many, many times on this site](http://stackoverflow.com/questions/1604091/update-a-table-using-join-in-sql-server/1604212#1604212). — Aaron Bertrand, Sep 22 '14 at 15:46

score 1 · Accepted Answer · answered Sep 22 '14 at 15:09

1

An UPDATE with a JOIN:

UPDATE TableA
SET TableA.ValueB = TableB.ValuA
FROM TableA
JOIN TableB 
    ON TableA.IdB = TableB.IdA

Then delete extraneous rows:

DELETE FROM TableA
WHERE NOT EXISTS (SELECT 1 FROM TableB WHERE TableB.IdA = TableA.IdB)

answered Sep 22 '14 at 15:09

DavidG

113,891
12
217
223

But I also need to handle the case where TableB has more rows (insert on TableA) and where TableB has less rows (a delete on TableA) – Samantha J T Star Sep 22 '14 at 15:10
Added another statement to do the DELETE – DavidG Sep 22 '14 at 15:12

How can I update the data in a base SQL table based on data in another table?

1 Answers1