Update SQL Server table with content from another table

Question

I need to populate the contents of a SQL Server table with the contents of another.

I have one table, Document Items, which contains (say) VendorPartNumber and UnitCost columns.

I then have another table, PO Items, with VendorPartNumber and UnitCost.

What do I need to do to get the relevant column contents from DocumentItems into PO Items?

What is your means of associating what [Document Item] goes to which [PO Item]? — Max Sorin, Mar 02 '16 at 20:16
Copy your post title, paste it in Google, and your answer is on the first page. — dfundako, Mar 02 '16 at 20:17

score 1 · Answer 1 · answered Mar 02 '16 at 20:24

1

update dbo.[PO Items]

set 
    VendorPartNumber = di.VendorPartNumber,
    UnitCost = di.UnitCost
from DocumentItems di 

where [PO Items].[{key column name}] = di.[{key column name}]

answered Mar 02 '16 at 20:24

Max Sorin

1,042
6
14

score 0 · Answer 2 · edited Mar 02 '16 at 20:39

If it is for one time operation:

If Part Oder Number is unique key in both tables, you can use the sub select statement from source table and set the values to 2nd table.

Ex:

update PO Items 
set VendorPartNumber = (select VendorPartNumber 
                        from Document Items 
                        where partOrdernumer = ?), 
    UnitCost = (select UnitCost 
                from Document Items 
                where partOrdernumer = ?) 
where partOrdernumer = ?

Else use triggers to update second table.

score -1 · Answer 3 · answered Mar 02 '16 at 20:18

-1

You can try with:

insert into POItems (VendorPartNumber, UnitCost) values (select VendorPartNumber, UnitCost from DocumentItems);

You can add where clause if you need.

answered Mar 02 '16 at 20:18

Claudio

642
3
12

Update SQL Server table with content from another table

3 Answers3