Is there a way to copy data from table A into Table B if it does not exist in Table B?

Question

I have two table in two different servers, but the servers are linked. I would like to compare Table A from one server and Table B from the other. If table B has a record that Table A does not have I would like to copy that record and insert it into Table A. I have tried to use Insert Into Select statements, but I cannot get it to execute. Any help would be appreciated. Thank you

I have tried to use Insert Into Select statements, but I cannot get it to execute.

INSERT INTO PHYSICAL_INVENTORY  (ITEMKEY, ITEM_NUMBER, WHSE_BIN_KEY, 
CONTROL_NUMBER)
SELECT T.ItemKey, I.ItemID, T.WhseBinKey, T.CtrlNo
from [Server B].prod.dbo.counttran T
inner join [Server B].prod.dbo.timItem I on I.ItemKey = T.ItemKey
Where ITEMKEY <> T.ItemKey

It looks like that should execute but return no rows (because you join on `itemkey = itemkey` but then filter to `itemkey <> itemkey`, and it can't be both). Is it throwing an error or just returning no results? The query needs to be tweaked for sure, but if it isn't executing then that's indicative of a different problem as well. — Aaron Dietz, Dec 20 '18 at 16:59
Possible duplicate of [INSERT VALUES WHERE NOT EXISTS](https://stackoverflow.com/questions/17991479/insert-values-where-not-exists) — Parfait, Dec 20 '18 at 17:17
Another possible duplicate of [How can I do an insert where not exists?](https://stackoverflow.com/q/4707063/1422451) — Parfait, Dec 20 '18 at 17:19
Still other possible duplicate of [SQL Server Insert if not exist](https://stackoverflow.com/questions/20971680/sql-server-insert-if-not-exist) — Parfait, Dec 20 '18 at 17:20
Aaron it does throw generic errors, like invalid something before itemkey and other variations of that — Ashley, Dec 20 '18 at 20:34

score 0 · Accepted Answer · answered Dec 20 '18 at 17:01

Maybe a NOT EXISTS is what you search for.

INSERT INTO physical_inventory
            (itemkey,
             item_number,
             whse_bin_key, 
             control_number)
            SELECT b.itemkey,
                   b.itemid,
                   b.whsebinkey,
                   b.ctrlno
                   FROM [Server B].prod.dbo.counttran b
                   WHERE NOT EXISTS (SELECT *
                                            FROM physical_inventory a
                                            WHERE a.itemkey = b.itemkey
                                                  AND a.item_number = b.itemid
                                                  AND a.whse_bin_key = b.whsebinkey
                                                  AND a.control_number = b.ctrlno);

(The WHERE in the exists can possibly be simplified if the tables have primary keys and it is enough to compare them.)

Sorry for the late reply got wrapped up in something else. I will try your solution sticky bit. — Ashley, Dec 20 '18 at 20:32

Is there a way to copy data from table A into Table B if it does not exist in Table B?

1 Answers1