sql - appending staging table to main, based on multiple columns, lots of nulls -

sql - appending staging table to main, based on multiple columns, lots of nulls -

bringing rows accounting scheme staging table. want append new recs in staging table main table. there no primary key because there no unique identifier in row besides looking @ every column. lots of nulls because not every column needs filled in.

example data:

staging: budget_line|date |fund|amount|description|po_number 1 |20140623 |xyz |12.00 |donut |{null} 1 |{null} |xyz |3.00 |{null} |12345 1 |20140623 |abc |4.00 |tire |{null} 2 |20140623 |xyz |12.00 |donut |{null} 1 |20140623 |xyz |12.00 |bobs donut |{null} main: budget_line|date |fund|amount|description|po_number 1 |20140623 |xyz |12.00 |donut |{null} 1 |{null} |xyz |3.00 |{null} |12345 1 |20140623 |abc |4.00 |tire |{null}

i've been doing this:

insert main select budget_line ,date ,fund ,amount ,description ,po_number staging not exists ( select budget_line ,date ,fund ,amount ,description ,po_number main ((staging.budget_line = main.budget_line) or (staging.budget_line null , main.budget_line null)) , ((staging.date = main.date) or (staging.date null , main.date null)) , ((staging.fund = main.fund) or (staging.fund null , main.fund null)) , ((staging.amount = main.amount) or (staging.amount null , main.amount null)) , ((staging.description = main.description) or (staging.description null , main.description null)) , ((staging.po_number = main.po_number) or (staging.po_number null , main.po_number null)) )

i'm getting aren't coming on , can't figure out why. have 28 fields though. there easier way this?

you've got problem null handling..

try instead - concatenates of fields single key column, , tests that:

insert main select budget_line ,date ,fund ,amount ,description ,po_number staging not exists ( select 1 main staging.budget_line & staging.date & staging.fund & staging.amount & staging.description & staging.po_number = main.budget_line & main.date & main.fund & main.amount & main.description & main.po_number)

sql ms-access