Updating a SQL table from a Local table - which is faster?

[Guest]

Hi,

I have a SQL table at the server that I need to update with a large Local
table. Is it better to:

a) Use an update query linking the SQL table to the local table. Or
b) Move the local table to the SQL server and then do the update query
between the two of them.

Perhaps I should clarify that the server is located in another state far
from where I am.

Thank you,

Trauton

 

[Rick Brandt]

Hi,

I have a SQL table at the server that I need to update with a large
Local table. Is it better to:

a) Use an update query linking the SQL table to the local table. Or
b) Move the local table to the SQL server and then do the update query
between the two of them.

Perhaps I should clarify that the server is located in another state
far from where I am.

Thank you,

Trauton

I would use DTS on the server to "pull" the data from the Access table if I
wanted the fastest method. That uses a bulk copy method that is not logged
on the server and can be very fast. The append query is likely to be the
slowest method.

 

[Guest]

Rick,

Thank you, you've given me an alternative that I hadn't considered. I am
familiar with DTS but have only used it to copy Access tables to the SQL
server. Could you perhaps give me an idea of how would you pull info from
table B (Access) to table A (SQL) using some fields from B to overwrite the
same fields on A?

 

[Rick Brandt]

Rick,

Thank you, you've given me an alternative that I hadn't considered.
I am familiar with DTS but have only used it to copy Access tables to
the SQL server. Could you perhaps give me an idea of how would you
pull info from table B (Access) to table A (SQL) using some fields
from B to overwrite the same fields on A?

I hadn't really picked up that this was an "update". I suppose you could pull
it into a temp table on the server and then run an update query from that. Not
sure you can use DTS to do the update directly.

 

[Guest]

Thank you Rick. I appreciate your help.

I hadn't really picked up that this was an "update". I suppose you could pull
it into a temp table on the server and then run an update query from that. Not
sure you can use DTS to do the update directly.