Duplicates with Append Query

[jstarace]

Can anyone provide suggestions on the following situation:

I have a ODBC bridge for access that connects to our website sql server. I need to periodically (once or twice a week), pull the user information from the web server and append it to a table in our local database. Problem is append queries append all the data from the webserver everytime the query is run. I need a solution that will only append the information that has been added to the website since the last append.

I have read about adding a WHERE NOT EXIST statement to the append query, is this the right direction?

Can someone give advise in the matter?

Thank you

 

[Golom]

WHERE NOT EXISTS is a possibility but it really depends on what information you have to work with. If, for example, you have a date stamp on your records then doing an APPEND of only those records where the date stamp is greater than the latest date stamp in your local database may be another possibility.

 

[willir]

jstarace

SQL is faster and more effecient, but I would be inclined to use VBA coding to test the conditions before updating / appending. This also gives you more flexibility where various tables can be updated through a single pass of the data.

Since the process will be run on an on-going basis, the code would be re-usable, and adapt to be running any time.

 

[MichaelRed]

Look (pderhaps carefully) at using a UPDATE query. The drawback is (possibly) including changes. It appears that many non-professional programmers have not actually read and understood this type/category of query.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Golom]

To MichaelRed

... and quite a few "professional" ones too.

 

[MichaelRed]

Golom ,

... depending on the local definition of 'professional'?



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[jstarace]

Hey thanks for all the advice. I was able to use the Where Not exist clause to only append the records where the primary key was not already in the table appending to.

example:


INSERT INTO tbltestappend ( Company, [E-mail], [Name 2], [Name 1], [Address 1], PhotoID )
SELECT SQL_AB22000_aa_Users.Company, SQL_AB28000_aa_Users.Email, SQL_AB28000_aa_Users.First, SQL_AB28000_aa_Users.Last, SQL_AB28000_aa_Users.Address1, SQL_AB28000_aa_Users.UserID
FROM SQL_AB28000_aa_Users
WHERE NOT EXISTS (
SELECT *
FROM tbltestappend
WHERE PhotoID = SQL_AB28000_aa_Users.UserID);