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.