Append query

[kabir_hussein]

Hi due to other problems i have made two tables that are similar except one holds the main data while the other one is used for simple entering new date.

To make the second table save all the data to the main table i have designed an append query where i add new data to the second table and then the data is appended to the main table.

However at present my query does not append as it does not recognise any new data. Is there any other solution i can do to make this work

below is the SQL for my query

INSERT INTO tenderlink ( TenderlinkID, PartID, SupplierID, [Lead Week], [Basis Of Supply], [Tender Quantity], [Tender Ref], [Tender Unit Price], [Target Unit Price], [Tender Cost], [Cross Reference to Cleint Spec], New )
SELECT tenderNew.tenderlinkid, tenderNew.PartID, tenderNew.SupplierID, tenderNew.[Lead Week], tenderNew.[Basis Of Supply], tenderNew.[Tender Quantity], tenderNew.[Tender Ref], tenderNew.[Tender Unit Price], tenderNew.[Target Unit Price], tenderNew.[Tender Cost], tenderNew.[Cross Reference to Cleint Spec], tenderNew.New
FROM tenderlink INNER JOIN tenderNew ON tenderlink.TenderlinkID = tenderNew.tenderlinkid;

 

[FoFa]

INSERT INTO tenderlink ( TenderlinkID, PartID, SupplierID, [Lead Week], [Basis Of Supply], [Tender Quantity], [Tender Ref], [Tender Unit Price], [Target Unit Price], [Tender Cost], [Cross Reference to Cleint Spec], New )
SELECT tenderNew.tenderlinkid, tenderNew.PartID, tenderNew.SupplierID, tenderNew.[Lead Week], tenderNew.[Basis Of Supply], tenderNew.[Tender Quantity], tenderNew.[Tender Ref], tenderNew.[Tender Unit Price], tenderNew.[Target Unit Price], tenderNew.[Tender Cost], tenderNew.[Cross Reference to Cleint Spec], tenderNew.New
FROM tenderlink INNER JOIN tenderNew ON tenderlink.TenderlinkID = tenderNew.tenderlinkid;

Your select is pulling from the same table you want to inset into, and only where the keys match on both tables (inner join). I am going to assume if these are new records they would NOT have matching keys in the two tables. Hence you SQL shoulod look something like this:

INSERT INTO tenderlink ( TenderlinkID, PartID, SupplierID, [Lead Week], [Basis Of Supply], [Tender Quantity], [Tender Ref], [Tender Unit Price], [Target Unit Price], [Tender Cost], [Cross Reference to Cleint Spec], New )
SELECT tenderNew.tenderlinkid, tenderNew.PartID, tenderNew.SupplierID, tenderNew.[Lead Week], tenderNew.[Basis Of Supply], tenderNew.[Tender Quantity], tenderNew.[Tender Ref], tenderNew.[Tender Unit Price], tenderNew.[Target Unit Price], tenderNew.[Tender Cost], tenderNew.[Cross Reference to Cleint Spec], tenderNew.New
FROM tenderNew LEFT JOIN tenderLink ON tenderlink.TenderlinkID = tenderNew.tenderlinkid WHERE tenderlink.TenderlinkID is null;

 

[kabir_hussein]

many thanks for that