Insert into...else update

1

I am using VBA to update an Access table from a query. The query may have multiple values for the primary key in the table. I have code that will delete the existing table data but I now need to append data from the row of the duplicate key to the first row of the primary key. There are additional columns in the table to accommodate the name of the individual with the duplicate key.

Query
12345 John Smith
54321 Bob Evans
12345 Mary Smith

Table after insert
12345 John Smith Mary Smith
54321 Bob Evans

2

Merge?

If you want UPSERT then either:

Do an INSERT and for the ones that fail do the UPDATE instead
or
Do an UPDATE and for the ones that fail do the INSERT

Best to choose whichever of those is more common (based on the first action), as that will perform better. Sounds like you have relatively few DUPs? so do INSERT first.

3

@scottdg,
This is a SQL Server Forum however I may have found a solution:
http://allenbrowne.com/ser-42.html