>> Hi,
>> Our application work with two databases same time. One in Server, and another local to be used if Our network go off-line. One of the tables hold products information, and his local copy is read-only.
>> We are wondering what are the bests ways to update the table (or replicate it, if you prefer this words) with the data from server.
>> Actually we do a "select everyone" from server create a script to do a mass "update or insert" and then run the script.
>> We could also do a local mass delete and then a mas insert with the almost same script (it would be not an "update or insert", but just "insert" statements).
>> We thought about do a parametrized query (as SET suggested days before in another thread message/114093) but we're not sure that we will gain in performance letting the transaction with the server >>open till we do the mass insert. Are we wrong?
>> Any info on what are the best methods?
>>
>> note: this table will never be more than 50.000 rows, as we expect.
>>
>
>In true, we need run the mass delete before or we will could leave some deleted rows in the table, leading to a not equal state in the local table...
>
>So seems to me that, "Insert or Update" will not be a good idea in this specific case...
>
>Any other ideas?

Are there many changes to this table? If not, you could have - well, more or less a copy of it that you populate through a trigger when changes happens to the original table. This copy would have to contain a few additional fields - a new primary key since a record might be updated several times before deletion and a field saying whether the trigger that fired was due to adding a record, modifying it or deleting. Then you could just modify your client database with what was inserted into this copy table since last time the client updated its table.

Of course, if the server deletes 20000 rows each day and adds 20000 new rows and you only update the client once a month, then this is a stupid idea.

HTH,
Set