Updating a table with modified records from another table

[Pamilerin]

I am trying to insert values from a table to another table. The thing is I want it such that only a row in the source table that does not exist in the destination table should be inserted. I have set each table to have a column that automatically increments so if there are 6 rows in table 1 and only 5 rows in table 2 , only row 6 in table 1 should be inserted into table 2. Here is my code

`INSERT INTO Portfolio
  ([SYMBOL], [SECURITY],[DATE],[OPENING PRICE],[CLOSING PRICE],[UNITS],[BROKER], [BROKERAGE COMMISSION],
   [SECURITY LEVY],[CONTRACT STAMP],[VAT],[NET VALUE],[PER SHARE VALUE],[PURCHASE PRICE],[GAIN / LOSS],[GAIN/LOSS %])

SELECT
    Company.[SYMBOL], Company.[SECURITY],BuyPortfolio.[DATE],Pricelist.[OPENING PRICE],Pricelist.[CLOSING PRICE],BuyPortfolio.[UNITS],BuyPortfolio.[BROKER],BuyPortfolio.[BROKERAGE COMMISSION], 
    BuyPortfolio.[SECURITY LEVY],BuyPortfolio.[CONTRACT STAMP],BuyPortfolio.[VAT],BuyPortfolio.[NET VALUE],
    BuyPortfolio.[PER SHARE VALUE], BuyPortfolio.[PURCHASE PRICE],
    ((Pricelist.[CLOSING PRICE]* BuyPortfolio.[PER SHARE VALUE])-BuyPortfolio.[PURCHASE PRICE]) AS "GAIN / LOSS",
    (((Pricelist.[CLOSING PRICE]* BuyPortfolio.[PER SHARE VALUE])-BuyPortfolio.[PURCHASE PRICE])/BuyPortfolio.[PURCHASE PRICE]) * 100 AS "GAIN/LOSS %"


FROM Company JOIN Pricelist
on Company.symbol = Pricelist.symbol  JOIN BuyPortfolio
on Pricelist.symbol = BuyPortfolio.symbol

WHERE BuyPortfolio.[STOCK ID] NOT LIKE Portfolio.[STOCK ID]'

The Portfolio is the destination table and the BuyPortfolio is the source table. What i cannot get is how to structure the WHERE clause.So if anyone can help me get it right or an alternate procedure on how to do it, I would be very grateful
Thanks.

[|-|x]

Assuming the autoincrement ID's do match up in both tables you could use a subquery like so...

...
WHERE BuyPortfolio.[STOCK ID] NOT IN (SELECT [STOCK ID] FROM Portfolio)

This will return all records in the BuyPortfolio table which are not in the Portfolio table according to the ID's.

[BitBlt]

You can't assume that the autoincrementing ID's will match up, so I'm pretty sure @Hearth's solution would not get the desired results.

It might be useful to know more about your source table. Is there a unique natural key to use? If so, you could LEFT JOIN to Portfolio on BuyPortfolio.[SYMBOL] = Portfolio.[SYMBOL] (and whatever else is in the natural key), and use this WHERE clause;

WHERE Portfolio.[SYMBOL] IS NULL

It would probably perform better than using "NOT IN", too.

[Pamilerin]

@Hearth Thanks your Solution worked for me .Thanks