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.

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.

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.

commented: I prefer your way, less resources and faster +8

@Hearth Thanks your Solution worked for me .Thanks

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.