Re: Copying Data from Access to SQL Server

TC wrote in message <1151910110.281840.316230@xxxxxxxxxxxxxxxxxxxxxxxxxxxx> :
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.

Have you tried Openrowset (whatch for linebreaks)?
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_oa-oz_78z8.asp

say a one field/one table thingie, create an SP

CREATE PROCEDURE dbo.uspCopyTable
AS

INSERT INTO
dbo.mytable (myfield)

SELECT
myfield
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\mypath\mydb.mdb';'admin';'mypwd', mytable)
GO

fire it off by something like this

dim cn as adodb.connection
set cn = new adodb.connection
cn.open "Provider=sqloledb;" & _
"Data Source=myServerName;" & _
"Initial Catalog=myDatabaseName;" & _
"Integrated Security=SSPI"
cn.execute "dbo.uspCopyTable"

--
Roy-Vidar


.

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: 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)
  • Copying Data from Access to SQL Server
    ... I need an automated procedure to copy data from an Access table to a ... SQL Server table. ... clean technique, but is extremely slow (about 10x slower than the file ... That was even slower (about 30x slower than the ...
    (comp.databases.ms-access)
  • 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