Insert new records only where that record doesn’t already exist. - Temp tables (1 Viewer)

Firepen

New member
Local time
Today, 16:15
Joined
Aug 4, 2020
Messages
7
Hi Guys,

This is a single step in a data import and transformation Mod I am working on, I will need to reapt the step a fair few times. The import is for a number of tables that support cascade combo boxes. Tables mostly contain ids from the lower tier tables (so not to repeat data). The IDs are auto numbers and manual entries/deletions exist so I only want to append new, unique entries pulled from a new set of data to be added into the combo boxes underlying tables.

I have temporary tables that hold the new data, I have already removed all the duplicates and added in the correct IDs to the table in question. Now I need to add the new records from tblMakesTemp to Makes. Specifically;

tblMakesTemp.Nbody into Makes.BodyTypeID

And

tblMakesTemp.NMake into Makes. MakeName

But I don’t want to add it if there is already a record that matches those fields. From previous advices I am staying away from a DAO record set approach.

I see two options;

As seen in table tblMakesTempWithOLD I can still have the old entries in the temp table. I could make a field that is the combined values of the two required fields but just for OLD entries (which have a field that makes clear they are old) then make this field only allow unique values, then copy across the same combo for the new data. Now only unique entries that do not already exist will be allowed to be copied over and then I just delete the unwanted fields and records which self-identify and i'm left with only the stuff i want and can append to the main table. This would work but feel like the long way around.

Second option is to use INSERT INTO. Which I tired first, only they are two unrelated tables, and one is purely temporary. I feel like I would need a join, only there is no field that connects them. Also I don’t want only matching records but all records to be compared. My experience with joins is all based on normalised permanent tables with Primary and foreign keys and there it all works fine.

This is what I have but it doesn’t know what to compare without a join and I am not sure how to add without a relating field. I am also not 100% sure about the WHERE clause.

Code:
Dim SQL As String

SQL = "INSERT INTO Makes (BodyTypeID, MakeName) " & _

"SELECT NBody, NMake " & _

"FROM tblMakesTemp" & _

"WHERE tblMakesTemp.NBody <> Makes.BodyTypeID AND tblMakesTemp.NMake <> Makes.MakeName;"

DoCmd.RunSQL SQL

End Sub

I think I need a sense check before I end up going down the wrong road again. What do you guys think. My test db is attached and let me know if the above didn’t make sense or you need more info.
 

Attachments

  • Test.zip
    366.8 KB · Views: 327

CJ_London

Super Moderator
Staff member
Local time
Today, 16:15
Joined
Feb 19, 2013
Messages
16,553
I'm surprised your sql works as there is no space before WHERE - advise to put the space at the beginning of the line so it is immediately apparent

perhaps something like this?

Code:
SQL = "INSERT INTO Makes (BodyTypeID, MakeName)" & _
" SELECT NBody, NMake " & _
" FROM tblMakesTemp LEFT JOIN Makes ON tblMakesTemp.NBody = Makes.BodyTypeID AND tblMakesTemp.NMake = Makes.MakeName" & _
" WHERE Makes.BodyTypeID is Null"
 

Firepen

New member
Local time
Today, 16:15
Joined
Aug 4, 2020
Messages
7
Thank CJ, that was exactly what I needed. A quick datatype change and it all works. I was looking at Joins but W3 and my book only do very basic examples of any thing they show. I knew a non-match from right table gave a null value but hadn't seen it used that way. Now I've gone though it all carefully it's all clicked into place.

I think I took the space out at some point while playing around. I will also switch to putting spaces at the start, you are right, it's much better way, I just went with what my book used as still learning.

Thanks Again!
 

isladogs

MVP / VIP
Local time
Today, 16:15
Joined
Jan 14, 2017
Messages
18,186
Although you've now got a working solution, I would recommend that next time you use the unmatched query wizard to get a list of all records that don't already exist. Then change it to an APPEND query. Job done!
 

Firepen

New member
Local time
Today, 16:15
Joined
Aug 4, 2020
Messages
7
Hi isladogs,

Thanks for the tip, I tried that and it does most of the work. I can only seem to add one set of matching fields in the wizard (1 from from either table) but it's a doddle to add more in the design view or in SQL so a very useful tip. Much appreciated!

Thanks Guys
 

Users who are viewing this thread

Top Bottom