>I have a remote table which I made into a view.
>I also have a main table which is part of a database.
>
>I want to merge the remote view table records using a SELECT SQL command into the main table. If the record IDs match in each table replace the older main table record with the newer remote view table record or don't replace the main table record if it is newer than the remote view table record.
>
>Also, I want to merge all the remote view table records into the main table if they are not in the main table at all.
>
>I have tried a few SELECT commands, but haven't quite got what I want. Thanks for helping.

Hi Steve,

I'd do this with three separate SELECT statements. Obviously, you'd want to replace the words RemoteView, LocalView, RecID and dUpdated in my code below with your actual view and field names (g).

*== Code snippet begins

*-- These are all the newer-on-the-remote records
SELECT RemoteView.RecID, ;
FROM RemoteView, LocalView ;
INTO CURSOR UpdRecs ;
WHERE RemoteView.RecID = LocalView.RecID ;
AND RemoteView.dUpdated > LocalView.dUpdated

*-- These are all the remote-only records
SELECT RemoteView.RecID, ;
FROM RemoteView ;
INTO CURSOR NewRecs ;
WHERE RemoteView.RecID NOT IN ;
( SELECT LocalView.RecID FROM LocalView )

*-- These are all the remaining local records
SELECT LocalView.RecID, ;
FROM LocalView ;
INTO CURSOR OldRecs ;
WHERE LocalView.RecID NOT IN ;
( SELECT RemoteView.RecID FROM RemoteView )
AND LocalView.RecID NOT IN ;
( SELECT UpdRecs.RecID FROM UpdRecs )

*-- Create a new table with the desired structure
CREATE TABLE NewTable ;
< desired fields >

*-- Combine the cursors into the new table
SELECT NewTable
APPEND FROM OldRecs
APPEND FROM NewRecs
APPEND FROM UpdRecs
USE IN NewTable

*== Code snippet ends

Because they're read-only cursors, you *can* create one index tag on them if you desire rather than using ORDER BY (it'll also be faster that way). You may wish to direct the SELECT statments to TABLE instead of CURSOR, if you want to save the intermediate steps for some reason.

HTH,
Evan Pauley

Evan Pauley, MCP
Positronic Technology Systems LLC
Knoxville, TN

If a vegetarian eats vegetables, what does a humanitarian eat?