Append Records From One Table to Another

[Sprinks]

In a DIY synchronization scheme, I have a Projects table on each user's hard
drive. When it comes time to resync their local tables to the server
back-end, I want to add any newly added local projects to the server file.
Can someone tell me the required SQL?

In pseudocode:

INSERT INTO Projects (ProjectNumber, ProjectDescription )
SELECT ProjectDummy.ProjectNumber, ProjectDummy.ProjectDescription
FROM ProjectDummy
WHERE <corresponding record in Projects does not yet exist>

Thank you.
Sprinks

 

[Damian S]

Sounds like you are going to be in a world of hurt with this method
Sprinks... how are you going to ensure that the integrity is maintained?

Be that as it may... the where clause you are after is as follows:

where ProjectDummy.ProjectNumber not in (select ProjectNumber from Projects)

Hope this helps.

Damian.

 

[John W. Vinson]

In a DIY synchronization scheme, I have a Projects table on each user's hard
drive. When it comes time to resync their local tables to the server
back-end, I want to add any newly added local projects to the server file.
Can someone tell me the required SQL?

I agree in general with Damian's comments, but I'd suggest a
more-efficient "frustrated outer join" query rather than a NOT IN
clause:

INSERT INTO Projects (ProjectNumber, ProjectDescription )
SELECT ProjectDummy.ProjectNumber, ProjectDummy.ProjectDescription
FROM ProjectDummy
LEFT JOIN Projects
ON Projects.ProjectNumber = ProjectDummy.ProjectNumber
WHERE Projects.ProjectNumber IS NULL;

John W. Vinson [MVP]

 

[Sprinks]

Thanks to both of you for the help.

Re: the synchronization, I don't understand the concern. The project
record is a ProjectNumber (the primary key) and a ProjectDescription. If the
record exists, I won't add it to the table, if it doesn't, I will. Are you
referring to possibility of someone adding the record in the small space
between another checking to see if it exists and someone else adding it?

If so, even though the chance of that is extremely remote in a 15-person
office, it should be handled. We would just allow the overwrite to take
place--the description isn't critical, only the number.

Other tables being updated have no potential conflicts--the only parent
record/detail records affected are "owned" by the user doing the sync. Other
than the Project record, he or she is not updating any records that others
have access to.

Am I missing another issue?

Thank you.
Sprinks