Copying Data from Access to SQL Server

I need an automated procedure to copy data from an Access table to a
SQL Server table. Speed is important. What is the recommended
technique?

I can export the data from Access, copy it via FTP, then import it into
SQL Server. I've tried that, and the speed is acceptable. It is an ugly
solution, however, and I expect to find a better one -- preferably a
solution better integrated with the Access RDBMS.

I've tried using an ODBC connection and an INSERT query. That's a good,
clean technique, but is extremely slow (about 10x slower than the file
copy technique).

I've also tried using an OLEDB connection and ADO recordsets to copy
the data procedurally. That was even slower (about 30x slower than the
file copy).

None of this makes sense to me. Why can't Access transfer data to SQL
Server through ODBC or OLEDB virtually as fast as the network can carry
the data? There are no indexes or triggers involved, and no transaction
processing -- just a simple column-for-column transfer of data from one
table to another. I feel as though I must be missing something obvious.
Can anyone offer any insight?


-TC


By the way, I'm using Access 2003 and SQL Server 2000. The network is a
WAN and I'm using TCP/IP as the default protocol.

.

Relevant Pages

  • Re: ORDER BY and IDENTITY
    ... how SQL Server actually behaves. ... > I view Technique 1 and Technique 3 as being basically the same, ... > something does not work is no proof or guarantee that it does work. ... >> Technique 2) to produce identity values that don't reflect the sort. ...
    (microsoft.public.sqlserver.programming)
  • Re: ORDER BY and IDENTITY
    ... I do not have the SQL Server ... I view Technique 1 and Technique 3 as being basically the same, ... that a public statement from Microsoft should be considered proof. ... > Technique 2) to produce identity values that don't reflect the sort. ...
    (microsoft.public.sqlserver.programming)
  • Re: Copying Data from Access to SQL Server
    ... I can export the data from Access, copy it via FTP, then import it into SQL Server. ... That's a good, clean technique, but is extremely slow (about 10x slower than the file copy technique). ... dbo.mytable (myfield) ...
    (comp.databases.ms-access)
  • Re: ADO stored proc
    ... could you recommend a book - vbscript or sql server? ... I've looked at vbscript books and sql server books on ... I don't like this technique since: ...
    (microsoft.public.scripting.vbscript)
  • Re: Bulk inserting into database using ADO.NET... deadly slow?
    ... program (look in your SQL Server documentation for "BULK INSERT"). ... > the DataSet, so to reduce the DB updates, but I found that when reaching ... > about 100K rows the DataSet becomes slower and slower, ... why is it so painfully slow to insert big chunks ...
    (microsoft.public.dotnet.framework.adonet)
Loading