dinol.softedge schrieb am 23.11.2008 um 14:55:41 (-0000):

> I have a table that I am using to populate another. The table is
> TBL_JOB_CARD_INVENTORY and I'm trying to populate TBL_INVENTORY by
> finding all records in TBL_JOB_CARD_INVENTORY that do not have
> matching records in TBL_INVENTORY.

> Problem is that some of the fields have differing data for each unique
> Inventory_ID. I am happy just to take the first instance of each
> record for each unique Inventory_ID.

For source T1 and equally-modelled target T2 and identity column A,
this translates into:

INSERT INTO T2
SELECT * FROM T1
WHERE NOT EXISTS (
SELECT 1 FROM T2
WHERE T2.A = T1.A
);

And for your situation:

INSERT INTO TBL_INVENTORY ( INVENTORY_ID, <other columns> )
SELECT INVENTORY_ID, <other columns>
FROM TBL_JOB_CARD_INVENTORY
WHERE NOT EXISTS (
SELECT 1 FROM TBL_INVENTORY
WHERE TBL_INVENTORY.INVENTORY_ID =
TBL_JOB_CARD_INVENTORY.INVENTORY_ID
);

Michael Ludwig

PS: Hi List, first-time poster here :-)