INSERT INTO without duplication (AC2007)

[AOB]

Hi guys,

Probably a dumb question but can't figure it out

I have a simple INSERT INTO statement, to add records to my main table from a series of linked tables (text files). I need to run the same INSERT INTO multiple times so I can eventually drop the linked text and just work off my normalised tables.

However - I only want to add new records. So if a particular combination already exists, I want the SQL to exclude those records from the INSERT INTO.

Normally I would just use something like :

INSERT INTO tblMain
SELECT DISTINCT ltbLink.fldField AS fldField
FROM ltbLink
WHERE ltbLink.fldField Not In (SELECT tblMain.fldField FROM tblMain)

But in this particular situation, the INSERT INTO is a combination of fields from different tables with various joins so it becomes more cumbersome.

Here is the basic SQL I have already :

INSERT INTO tblAccounts
 
SELECT DISTINCT TRIM(ltb1.[BANK ACCOUNT]) AS AccountNumber, tblCurrencies.CurrencyID AS CurrencyID, tblBanks.BankID AS BankID
 
FROM ((ltb1 
 
INNER JOIN tblCurrencies 
    ON TRIM(ltb1.[CURR CODE])=tblCurrencies.CurrencyCode)
 
INNER JOIN ltbDepBankLookup 
    ON TRIM(ltb1.[BANK ACCOUNT])=ltbBankLookup.[BANK ACCOUNT])
 
INNER JOIN tblBanks 
    ON TRIM(ltbBankLookup.[AGENT BANK])=tblBanks.BankAcronym

Now this will work, because I also have composite primary keys across the three fields in the main (tblAccounts) table. So duplicates will be prevented that way. But my autonumbers go right out of whack and I'd prefer to have them properly incremented for each genuinely new account combination.

So can anybody tell me how I can adapt the above query to only attempt to insert new records, and exclude the duplicates 'up-front'? Pretty sure it involves some kind of WHERE EXISTS but haven't the foggiest how to syntax it?...

Thanks!

Al

 

[informer]

Hi AOB

A little advice, create always an auto incremented field which is a technical primary Key (tPK)
Then add a unique index composed with the different fields which define your real functional PK (fPK)

Then If you try to add a record with existing fields composing your fPK, your insert will be refused

If you don't use an auto increment number, your foreign key will be awful !

 

[AOB]

Thanks informer - good advice which I do heed

An old post so can't quite remember but I think I got around the problem by incorporating a staging table into which I imported the raw data. Then applied some queries to refine it (i.e. remove duplicates based on field combinations and also based on existing data in the main tables) Then finally just add the refined data to the tables and dump the staging table. Much more stable and controlled and also ensures that only appropriate records are added to the main tables (thus ensuring the auto incremented field does not escalate wildly)

But thanks for the advice!

 

[Galaxiom]

A staging table is often a good idea when importing text. Bring everything in as text. This avoids any unexpected values being rejected as ImportErrors.

In answer to the original question:

Use an update query with outer joins from the source to destination tables on the fields that must form a unique combination. Put an Is Null criteria on the joined fields from the destination table. This will insert any records that do not match the destination records.

Using a composite unique index ("functional Primary Key" in informer's unconventional terminology) will certainly prevent any duplicate records being inserted and aught to be included in the design. However relying on that for the insert query will throw errors.