By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,289 Members | 1,796 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,289 IT Pros & Developers. It's quick & easy.

Appending from first table where not in second table

P: n/a
I want to verify that the following SQL insert statement is correct:

sSQL = "INSERT INTO [BACKUP_TABLE] (FIELD1, FIELD2, FIELD3, FIELD4,
FIELD5)" & _
" SELECT A.FIELD1, A.FIELD2, A.FIELD3, A.FIELD4, A.FIELD5" & _
" FROM [MAIN_TABLE] As A LEFT JOIN [BACKUP_TABLE] AS B ON" & _
" (A.FIELD1= B.FIELD1 AND" & _
" A.FIELD2 = B.FIELD2 AND" & _
" A.FIELD3 = B.FIELD3)" & _
" WHERE B.FIELD1 Is Null" & _
" AND B.FIELD2 Is Null" & _
" AND B.FIELD3 Is Null;"

CurrentDb.Execute sSQL, dbFailOnError + dbSeeChanges

The idea is that I want to insert into BACKUP_TABLE any records that are in
MAIN_TABLE but not in BACKUP_TABLE. The part I'm very unclear on is the LEFT
JOIN on three different fields and the WHERE clause with the three fields Is
Null.

Thanks.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200612/1

Dec 18 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a

rdemyan via AccessMonster.com wrote:
I want to verify that the following SQL insert statement is correct:

sSQL = "INSERT INTO [BACKUP_TABLE] (FIELD1, FIELD2, FIELD3, FIELD4,
FIELD5)" & _
" SELECT A.FIELD1, A.FIELD2, A.FIELD3, A.FIELD4, A.FIELD5" & _
" FROM [MAIN_TABLE] As A LEFT JOIN [BACKUP_TABLE] AS B ON" & _
" (A.FIELD1= B.FIELD1 AND" & _
" A.FIELD2 = B.FIELD2 AND" & _
" A.FIELD3 = B.FIELD3)" & _
" WHERE B.FIELD1 Is Null" & _
" AND B.FIELD2 Is Null" & _
" AND B.FIELD3 Is Null;"

CurrentDb.Execute sSQL, dbFailOnError + dbSeeChanges
Well, what happens when you create two small tables (like 3 records
each) and test it? Try the commonsense approach. Test it on a
manageable dataset and eyeball the results if you have to. Coding is
good. Common sense is better.

Dec 18 '06 #2

P: n/a
at a quick glance i think it should be ISNULL([fieldname])

"rdemyan via AccessMonster.com" <u6836@uwewrote in message
news:6aebd59932d63@uwe...
>I want to verify that the following SQL insert statement is correct:

sSQL = "INSERT INTO [BACKUP_TABLE] (FIELD1, FIELD2, FIELD3, FIELD4,
FIELD5)" & _
" SELECT A.FIELD1, A.FIELD2, A.FIELD3, A.FIELD4, A.FIELD5" & _
" FROM [MAIN_TABLE] As A LEFT JOIN [BACKUP_TABLE] AS B ON" & _
" (A.FIELD1= B.FIELD1 AND" & _
" A.FIELD2 = B.FIELD2 AND" & _
" A.FIELD3 = B.FIELD3)" & _
" WHERE B.FIELD1 Is Null" & _
" AND B.FIELD2 Is Null" & _
" AND B.FIELD3 Is Null;"

CurrentDb.Execute sSQL, dbFailOnError + dbSeeChanges

The idea is that I want to insert into BACKUP_TABLE any records that are
in
MAIN_TABLE but not in BACKUP_TABLE. The part I'm very unclear on is the
LEFT
JOIN on three different fields and the WHERE clause with the three fields
Is
Null.

Thanks.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200612/1


Dec 18 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.