user6305775 user6305775 - 1 year ago 65
SQL Question

Update and insert TSQL

How can i modify TSQL update statement below to insert

partid
of the source table is not in the partid field of Item ID ='500224' in destination table. if doesn't exist I need to insert.

USE [DBNAME];
GO
UPDATE m
SET [partId] = a.[partId]
select * FROM
[SourceTable] m
INNER JOIN
[DestTable] a
ON m.[bomItem] = a.[ItemId]
AND m.bomEntry = a.bomEntry AND m.bomRev = a.rev WHERE
m.bomRev=a.rev AND m.partId <> a.partid AND m.lineNbr = a.bomEntry ;
Go


In Source Table

enter image description here

In Destination Table

enter image description here

For Example in pictures above partid
100280
doesn't exist in Destination table i want to add that and keep all others the same.

Answer Source

You can insert the missing partid to the destination table, by using LEFT JOIN with NULL check.

INSERT INTO [DestTable] (bomItem, bomRev, bomEnty, lineNbr, dType, partId, revId, qty)
SELECT ST.ItemID, ST.rev, ST.bomEnty, ST.lineNbr, 0 AS dType, ST.partid, NULL AS revId, ST.qty
FROM [SourceTable] ST
LEFT JOIN [DestTable] DT ON DT.bomItem = ST.ItemID AND DT.partid = ST.partid
        AND DT.bomRev = ST.Rev AND DT.bomEntry = ST.bomEntry -- remove these conditions if not need
WHERE DT.partid IS NULL

Since few of the columns is mismatching between the two tables, I have added proper column names in the SELECT