Fast export to SQL server

[CedarTree]

I have coding that can quickly fill a local table from a remote server table... basically take an SQL snapshot and load the local table. In my application, I am filling a local table with calculated values (a few hundred rows), and if I then want to "export" to the Server, I can do it one of at least 2 ways:
1) Simple append query in Access that loads the remote table using local table as source... but this is SLOW
2) Pass-through query statements that load the remote table row by row... faster, but still very SLOW
Any other tricks of the trade?
Thanks!

 

[CJ_London]

Have you tried a stored procedure with parameters for the relevant fields? Runs a row at a time. We have one which loads a 2m row x 60 field file in about 8 mins. Using ssis takes a few seconds. Implication is perhaps your query/pass through is not efficient. Or perhaps you have a slow connection or the server is busy doing other things so you are at the bottom of the pile in terms of available resource

 

[CedarTree]

Thanks. Is there a good resource to learn about stored procedures? (I'm more of an Access person and know just the basics about Server). And I would still need to somehow pass the data over? Or do you write out a text file or something and have a stored procedure which imports in the data?

 

[CedarTree]

And how do you kick off the stored procedure from Access?

 

[Mark_]

Alternate is export your values to a CSV and have the SQL import.

Mostly depends on what is the actual cause of your slowdown.

 

[CJ_London]

You write stored procedures in sql server you call them with ado or using something like

Execute mySP param1, param2, .....,

In a passrhrough query

I dont have any recommended reading best to use the web