I have this exact same problem and the most effective way I found (and safest) was to go record by record using a remote view with row buffering enabled and tableupdate after each record INSERT. And watch out for blank/invalid dates.
>What would you say is the most effective/efficient way to insert many records at once into a SQL server table on an ongoing basis?
>
>Basically, we get anywhere between 5000 and 20000 records per day that must be added to this one table. The ugly part is that they all come in at once. If they came in at all hours of the day, I'd just insert them as they came in. Basically, I would like to know the best method (view vs. SPT) and the best settings for the connection and/or cursor/query. I really don't have the time (unfortunately) to test each combination of settings to see which one is fastest.
>
------------------------------------------------
John Koziol, ex-MVP, ex-MS, ex-FoxTeam. Just call me "X"
"When the going gets weird, the weird turn pro" - Hunter Thompson (Gonzo) RIP 2/19/05