Copying Data from Access to SQL Server

[TC]

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.

[Terry Kreft]

Re: Copying Data from Access to SQL Server

There are many ways to achieve what you want, the following list is not
exhaustive they just happen to be the ways I have done similar work.

You can either push the data from Access or pull the data from SQL Server.

Pushing the data
1) Recordset to Recordset
Copy the data row by row and field by field from one recordset to
the other
2) Recordset to Insert query
Create an Insert statement for each row in the recordset
3) Linked table insert
Link to the SQL Server table and use an Insert statement to insert
the data

Pulling the data
1) DTS package
Create a DTS package in SQL to pull the data, you can execute the
DTS package either from the Access database or by calling a stored procedure
on the SQL server which executes the DTS package.
2) Linked Server
You can create a linked server in SQL which points at your Access DB
you can then call a stored procedure to insert the data from the linked
server into your SQL table.

--

Terry Kreft


"TC" <golemdanube@ya hoo.comwrote in message
news:1151910110 .281840.316230@ m73g2000cwd.goo glegroups.com.. .

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.
>

[RoyVidar]

Re: Copying Data from Access to SQL Server

TC wrote in message
<1151910110.281 840.316230@m73g 2000cwd.googleg roups.com:

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.uspCopyTabl e
AS

INSERT INTO
dbo.mytable (myfield)

SELECT
myfield
FROM OPENROWSET('Mic rosoft.Jet.OLED B.4.0',
'c:\mypath\mydb .mdb';'admin';' mypwd', mytable)
GO

fire it off by something like this

dim cn as adodb.connectio n
set cn = new adodb.connectio n
cn.open "Provider=sqlol edb;" & _
"Data Source=myServer Name;" & _
"Initial Catalog=myDatab aseName;" & _
"Integrated Security=SSPI"
cn.execute "dbo.uspCopyTab le"

--
Roy-Vidar

[Lyle Fairfield]

Re: Copying Data from Access to SQL Server

If I understand what you require correctly:

I create an ADP file connected to the SQL Server / SQL Database.

To transfer Table "Temp" I run this code from the MDB file:

****
Const ADPFile As String = "C:\Documen ts and Settings\Lyle Fairfield\My
Documents\Acces s\FFDBABooks.ad p"

Sub temp()
DoCmd.TransferD atabase acExport, "Microsoft Access", _
ADPFile, acTable, "Temp", "Temp", False
End Sub
****

I cannot comment about speed as I have used this only for small tables,
but it seems instantaneous for them.

In my full code I loop through Table Names and transfer them all. (My
actual use for this is in reverse; I use it to keep a working MDB
backup on my local machne of my remote SQL Server Data).

Column Defintions are munged, of course, to comply with what SQL Server
thinks it is getting.

Indexes are not included in the Transfer.

After the Transfer the ADP is fully "automatabl e" of course. If I need
Append I supplement my code to run SQL in the ADP to do the Append and
to delete the Temporary Table, or I can run SQL to modify Column Types
and to Add Indexes.

[TC]

Re: Copying Data from Access to SQL Server

Thank you for all the suggestions. Here are the transfer rates I
achieve with various of the recommended techniques:

1. Export, FTP, and Import -- 280 rows/s
2. TransferDatabas e to ADP file -- 22 rows/s
3. INSERT into ODBC-linked table -- 18 rows/s
4. Procedural Loop through ADO Recordsets -- 6 rows/s

My question is really a conceptual one. Why is it so much faster to do
the transfer outside the RDBMS (1) than inside the RDBMS (2,3,4)?

Before doing this, I would have expected the opposite -- I would have
thought that techniques specifically designed to transfer data between
databases would be faster than generic techniques. In fact, the results
are so surprising, I feel I need a reality check. Is there really no
efficient way to transfer data directly between Access and SQL Server?

-TC

[Lyle Fairfield]

Re: Copying Data from Access to SQL Server

TC wrote:

Thank you for all the suggestions. Here are the transfer rates I
achieve with various of the recommended techniques:
>
1. Export, FTP, and Import -- 280 rows/s
2. TransferDatabas e to ADP file -- 22 rows/s
3. INSERT into ODBC-linked table -- 18 rows/s
4. Procedural Loop through ADO Recordsets -- 6 rows/s

Yikes ... some variance!

Do you export as a text file?

Do you FTP to the server where SQL-Server runs, or to a connected
Server?

Do you import with bcp?

[Rick Brandt]

Re: Copying Data from Access to SQL Server

TC wrote:

Thank you for all the suggestions. Here are the transfer rates I
achieve with various of the recommended techniques:
>
1. Export, FTP, and Import -- 280 rows/s
2. TransferDatabas e to ADP file -- 22 rows/s
3. INSERT into ODBC-linked table -- 18 rows/s
4. Procedural Loop through ADO Recordsets -- 6 rows/s
>
My question is really a conceptual one. Why is it so much faster to do
the transfer outside the RDBMS (1) than inside the RDBMS (2,3,4)?

I would imagine that a large part of the difference is because bulk operations
like the import are not logged. This is also why DTS transfers are very fast.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[TC]

Re: Copying Data from Access to SQL Server

Lyle,

I tried exporting a text file, an Access .mdb, and an Excel file. I get
the fast speeds with Access and Excel -- the text file is just too big.

I FTP to the same server where SQL Server is running.

I'm using DTS, not BCP.


-TC



Lyle Fairfield wrote:

TC wrote:

Thank you for all the suggestions. Here are the transfer rates I
achieve with various of the recommended techniques:

1. Export, FTP, and Import -- 280 rows/s
2. TransferDatabas e to ADP file -- 22 rows/s
3. INSERT into ODBC-linked table -- 18 rows/s
4. Procedural Loop through ADO Recordsets -- 6 rows/s

>
Yikes ... some variance!
>
Do you export as a text file?
>
Do you FTP to the server where SQL-Server runs, or to a connected
Server?
>
Do you import with bcp?

[TC]

Re: Copying Data from Access to SQL Server

Rick,

I saw the logging issue raised in another thread, so I turned on the
bulk copy option. I used a stored procedure with the following line:

EXEC sp_dboption @DatabaseName, 'select into/bulkcopy', 'True'

It didn't seem to make any difference, though. Did I do something
wrong?


-TC


Rick Brandt wrote:

>
I would imagine that a large part of the difference is because bulk operations
like the import are not logged. This is also why DTS transfers are very fast.
>
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Terry Kreft]

Re: Copying Data from Access to SQL Server

That 280 rows/s was that for the whole operation or just the export or just
the import?



--

Terry Kreft


"TC" <golemdanube@ya hoo.comwrote in message
news:1151941466 .112239.16080@m 73g2000cwd.goog legroups.com...

Thank you for all the suggestions. Here are the transfer rates I
achieve with various of the recommended techniques:
>
1. Export, FTP, and Import -- 280 rows/s
2. TransferDatabas e to ADP file -- 22 rows/s
3. INSERT into ODBC-linked table -- 18 rows/s
4. Procedural Loop through ADO Recordsets -- 6 rows/s
>
My question is really a conceptual one. Why is it so much faster to do
the transfer outside the RDBMS (1) than inside the RDBMS (2,3,4)?
>
Before doing this, I would have expected the opposite -- I would have
thought that techniques specifically designed to transfer data between
databases would be faster than generic techniques. In fact, the results
are so surprising, I feel I need a reality check. Is there really no
efficient way to transfer data directly between Access and SQL Server?
>
-TC
>

[david epsom dot com dot au]

Re: Copying Data from Access to SQL Server

No indexes, no logging: my next guess would be
that Access is sending the data one row at a time.
Turn on odbc or server tracing and see how the
data is comming across

(Or, of course, turning off ODBC logging if that
is turned on: that really slows things down :~)

(david)

"TC" <golemdanube@ya hoo.comwrote in message
news:1151951979 .595417.25710@b 68g2000cwa.goog legroups.com...

Rick,
>
I saw the logging issue raised in another thread, so I turned on the
bulk copy option. I used a stored procedure with the following line:
>
EXEC sp_dboption @DatabaseName, 'select into/bulkcopy', 'True'
>
It didn't seem to make any difference, though. Did I do something
wrong?
>
>
-TC
>
>
Rick Brandt wrote:

>>
>I would imagine that a large part of the difference is because bulk
>operations
>like the import are not logged. This is also why DTS transfers are very
>fast.
>>
>--
>Rick Brandt, Microsoft Access MVP
>Email (as appropriate) to...
>RBrandt at Hunter dot com

>

[Albert D.Kallal]

Re: Copying Data from Access to SQL Server

>

1. Export, FTP, and Import -- 280 rows/s
2. TransferDatabas e to ADP file -- 22 rows/s
3. INSERT into ODBC-linked table -- 18 rows/s
4. Procedural Loop through ADO Recordsets -- 6 rows/s
>
My question is really a conceptual one. Why is it so much faster to do
the transfer outside the RDBMS (1) than inside the RDBMS (2,3,4)?

I am not surprised one bit by the above.

#1 - FTP, and using the sql (enterprise manager) tools, it can read the raw
data.

#2,#3

How do you think a remote connection to sql works?

Ok, build a sql string. Now send the sql string. The query processor then
loads. The query process then parses out the string. The query processor
then checks the sql for syntax. The query processor then builds a query
plan. The query process then optimizes this query plan. The query then
executes the sql AND INSERTS ONE ROW of data!.

Now, send a whole new sql string for ht next record. And, you are surprised
this is slower then option one?

I don't think you should be surprised that this is going to be slower at
all...

Remember, when you use a odbc (or oleDB) connection, EACH data insert is
going to a full line of sql sent to the server. Often, just the sql syntax
overhead is GOING TO BE LARGER THEN HE ACTUAL DATA transferred for the ONE
record. Remember, EACH record is going to requite a FULL LINE OF sql string
to insert the data.

With the ftp, and using the sql tools, the use of a sql statement for EACH
LINE of data is by-passed....


what you need to do is find the syntax in sql for MULTI-RECORD inserts of
lines of data.

You then sent about 10, or 20 lines of data IN ONE sql pass-through
statement. Doing his would likely speed up the process by a factor of 10, or
20 times (in fact, the speed up would be the factor of how many lines of
data you include in each sql).

I reasonably sure that sql server supports a insert statement with MULTIPLE
lines of data...so, do some testing.

You code that loops would thus have to format he sql statement in code..and
send it as a pass-though to sql server...

Once you realize that a odbc, or remote connection can ONLY communicate with
sql server by sending FULLY formatted sql statements for EACH record, then
there should be little, if any surprise at the performance numbers you
posted...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKa llal@msn.com

http://www.members.shaw.ca/AlbertKallal

[TC]

Re: Copying Data from Access to SQL Server

Terry,

280 rows/s is for the whole operation.

I've also done some experiments with a local SQL Server. In that
scenario, there is no FTP step -- just export and import. It looks like
I'm getting a speed improvement of about 20x over the ODBC/append
technique.

Based on my observations so far, I must conclude that Access offers no
efficient way to append rows into SQL Server. Despite the extra steps
involved, it is far more efficient to export to a file, move the file
onto the server, then import into SQL Server. This is true even for a
local SQL Server.

I will put that statement up for debate. I'm curious to know whether
the experiences of other Access developers support or refute my
conclusion.


-TC


Terry Kreft wrote:

That 280 rows/s was that for the whole operation or just the export or just
the import?
>
--
Terry Kreft
>
>
"TC" <golemdanube@ya hoo.comwrote in message
news:1151941466 .112239.16080@m 73g2000cwd.goog legroups.com...

Thank you for all the suggestions. Here are the transfer rates I
achieve with various of the recommended techniques:

1. Export, FTP, and Import -- 280 rows/s
2. TransferDatabas e to ADP file -- 22 rows/s
3. INSERT into ODBC-linked table -- 18 rows/s
4. Procedural Loop through ADO Recordsets -- 6 rows/s

My question is really a conceptual one. Why is it so much faster to do
the transfer outside the RDBMS (1) than inside the RDBMS (2,3,4)?

Before doing this, I would have expected the opposite -- I would have
thought that techniques specifically designed to transfer data between
databases would be faster than generic techniques. In fact, the results
are so surprising, I feel I need a reality check. Is there really no
efficient way to transfer data directly between Access and SQL Server?

-TC

[TC]

Re: Copying Data from Access to SQL Server

Albert,

You have added something new to this discussion. Before this, I did not
know ODBC sends a different SQL statement for each row in an INSERT
statement. Can you provide more details? Is this a limitation of ODBC
only, or is it true also of OLEDB? I assume it is a problem only for
UPDATE, INSERT, and DELETE queries operating on SQL Server tables -- is
that right, or am I failing to see broader ramifications?

If the one-statement-per-row phenomenon is true (and it does have the
ring of truth to it), that suggests ODBC cannot be, and was probably
never intended to be, an efficient way of transferring bulk data
between databases. That brings me back to my original question: What is
an efficient way of transferring bulk data between databases?

I understand your suggestion about a multi-line pass-through query. I
doubt this will work in my case, however, since I want a solution
capable of sending memo/BLOB columns.


-TC


Albert D.Kallal wrote:

1. Export, FTP, and Import -- 280 rows/s
2. TransferDatabas e to ADP file -- 22 rows/s
3. INSERT into ODBC-linked table -- 18 rows/s
4. Procedural Loop through ADO Recordsets -- 6 rows/s

My question is really a conceptual one. Why is it so much faster to do
the transfer outside the RDBMS (1) than inside the RDBMS (2,3,4)?

>
I am not surprised one bit by the above.
>
#1 - FTP, and using the sql (enterprise manager) tools, it can read the raw
data.
>
#2,#3
>
How do you think a remote connection to sql works?
>
Ok, build a sql string. Now send the sql string. The query processor then
loads. The query process then parses out the string. The query processor
then checks the sql for syntax. The query processor then builds a query
plan. The query process then optimizes this query plan. The query then
executes the sql AND INSERTS ONE ROW of data!.
>
Now, send a whole new sql string for ht next record. And, you are surprised
this is slower then option one?
>
I don't think you should be surprised that this is going to be slower at
all...
>
Remember, when you use a odbc (or oleDB) connection, EACH data insert is
going to a full line of sql sent to the server. Often, just the sql syntax
overhead is GOING TO BE LARGER THEN HE ACTUAL DATA transferred for the ONE
record. Remember, EACH record is going to requite a FULL LINE OF sql string
to insert the data.
>
With the ftp, and using the sql tools, the use of a sql statement for EACH
LINE of data is by-passed....
>
>
what you need to do is find the syntax in sql for MULTI-RECORD inserts of
lines of data.
>
You then sent about 10, or 20 lines of data IN ONE sql pass-through
statement. Doing his would likely speed up the process by a factor of 10, or
20 times (in fact, the speed up would be the factor of how many lines of
data you include in each sql).
>
I reasonably sure that sql server supports a insert statement with MULTIPLE
lines of data...so, do some testing.
>
You code that loops would thus have to format he sql statement in code..and
send it as a pass-though to sql server...
>
Once you realize that a odbc, or remote connection can ONLY communicate with
sql server by sending FULLY formatted sql statements for EACH record, then
there should be little, if any surprise at the performance numbers you
posted...
>
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKa llal@msn.com
http://www.members.shaw.ca/AlbertKallal

[David W. Fenton]

Re: Copying Data from Access to SQL Server

"TC" <golemdanube@ya hoo.comwrote in
news:1152033470 .740516.69200@7 5g2000cwc.googl egroups.com:

Based on my observations so far, I must conclude that Access
offers no efficient way to append rows into SQL Server. Despite
the extra steps involved, it is far more efficient to export to a
file, move the file onto the server, then import into SQL Server.
This is true even for a local SQL Server.

Er, why would this kind of performance difference ever matter? That
is, how often is this going to be done in any real-world scenario?
I'd say: use the easiest method in terms of implementation and don't
worry at all about performance.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/