need help (1 Viewer)

syodb

Registered User.
Local time
Today, 04:24
Joined
Sep 7, 2017
Messages
27
Got a table1:
ID, field1, field2, field3, field4, field5, field6, field7
Got a table2:
ID, field1, field2, field3, field4, field5, field6, field7
ID is autonumber.
Trying to insert from Table1 field1, ... , field7 if field1, ... , field7 do not exist in table2.
my query:
Code:
INSERT INTO table2 ( field1,field2, field3, field4, field5, field6, field7 )
SELECT a.field1, a.field2, a.field3, a.field4, a.field5, a.field6, a.field7
FROM Table1 AS a
WHERE (a.field1 and  a.field2 and a.field3 and a.field4 and a.field5) not  IN (SELECT  
b.field1 and b.field2 and b.field3 and b.field4 and b.field5 FROM table2 b where a.field1=b.field1 
and a.field2=b.field2 and a.field3=b.field3 and a.field4=b.field4 and a.field5=b.field5);
my query is slow.
1) how to make it faster without using the ID?.
2) how to limit the insert to the top 10 IDs in Table1?.
 

June7

AWF VIP
Local time
Today, 03:24
Joined
Mar 9, 2014
Messages
5,423
Does this actually insert anything? I don't see how that WHERE clause can work at all. Uses of and makes no sense. Use & to concatenate fields.

Try SELECT TOP 10 … ORDER BY ID DESC.
 

isladogs

MVP / VIP
Local time
Today, 11:24
Joined
Jan 14, 2017
Messages
18,186
For the first question, try using the unmatched query wizard but linking by all of Field1 to Field7. Put the Is null filters for each field on the same line to AND them … or separate rows to OR them. When you are satisfied, convert to APPEND.

Hope that makes sense....:rolleyes:
 

syodb

Registered User.
Local time
Today, 04:24
Joined
Sep 7, 2017
Messages
27
isladogs, no i did not follow your suggestion.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:24
Joined
Feb 28, 2001
Messages
26,996
Those ANDs in the original query will try to do a "Boolean" addition of the individual fields. You are using AND in the human-language sense of the word but SQL is using it in the computer sense of the word, which is different.

Your first concern is the structure of a table with seven fields, none of which can be used individually to eliminate records in another table. You have to use all seven to allow an elimination.

I might take the approach of adding a "flag field" to table 1. Then set all flags to TRUE. Test all fields for being null and reset them to something not null (on both tables). Then run an UPDATE query that does a JOIN of all seven fields and sets the flag field false. Then do the insert only on records where the flag field is still TRUE.

Code:
UPDATE TableA SET FLAG = TRUE;

UPDATE TableA AS TA INNER JOIN TableB as TB ON ( TA.F1 = TB.F1 ) AND ( TA.F2 = TB.F2 ) ... etc ... AND ( TA.F7 = TB.F7 ) SET TA.FLAG = FALSE ;

When doing the insert on the records with flag = TRUE, the "INSERT" syntax would let you do the insert from less than all such flagged records because you can include an ORDER BY on the ID field and include the TOP predicate in the SELECT side of that INSERT INTO clause. But this way, you don't have to do the complex comparison at the same time that you are doing the insertion.
 

isladogs

MVP / VIP
Local time
Today, 11:24
Joined
Jan 14, 2017
Messages
18,186
If you are still unclear after reading Doc's post, then a recent thread may be of use https://www.access-programmers.co.uk/forums/showthread.php?t=303677&highlight=Q17+joins

If you can make sense of the idea, it should serve your purposes.
The method will cope with any number of fields and handle nulls.

However I do wonder whether your table structure is making this more difficult than it needs to be

If still confused after checking the above link, suggest you post a copy of your database with just those two tables and any queries you've tried
 

syodb

Registered User.
Local time
Today, 04:24
Joined
Sep 7, 2017
Messages
27
The_Doc_Man, isladogs thank you for the great help. June7 thanks.
 

Users who are viewing this thread

Top Bottom