Not Exist Join

[Mariners]

It was suggested to me to use a Not Exists Join to accomplish the below problem. I'm not sure how to write SQL for this.

I have the following:

1,000 records in table A

500 records in table B

Field ProductId is common to both tables.

If a ProductId from table A does not exist in B table a record needs to be added to table B. In this case 500 records from table A need to be added to table B.

 

[pdx_man]

Don't know about Not Exists Join, but ...

INSERT INTO TableB (ProductId, OtherField1, OtherField2 ...)
SELECT ta.ProductId, ta.OtherField1, ta.OtherField2 ...
FROM TableA ta
LEFT JOIN TableB tb
ON ta.ProductId = tb.ProductId
WHERE tb.ProductId IS NULL

 

[Mariners]

Forgot an important detail

Thanks for the help, the sql statement worked except I left out an important detail about Table A. It has duplicate ProductId records, there is a potential to have the same ProductId record many times in Table A. There is another field in Table A that called Period for example (200704).

 

[pdx_man]

Well, it depends on what you need in TableB. Are you looking for it to have unique instances of ProductID? Can there be the same ProductID with unique Period?

Check out the DISTINCT keyword:

INSERT INTO TableB (ProductId, OtherField1, OtherField2 ...)
SELECT DISTINCT ta.ProductId, ta.OtherField1, ta.OtherField2 ...
FROM TableA ta
LEFT JOIN TableB tb
ON ta.ProductId = tb.ProductId
WHERE tb.ProductId IS NULL

 

[Mariners]

DISTINCT Keyword

The DISTINCT keyword did the trick! Thanks.

Dave