Fastest Way to Export Data From Access to SQL Server

[agiamb]

My application uses ODBC connected tables to SQL Server 2008 R2 Express as
the back end, across a LAN.

One function is to import data from Excel files on the local machine,
perform some manipulation on the imported data and add some user input.

With an Access back end, I typically would import the Excel data into a
temporary local table, do the processing, then use an append query to insert
it into the live back end table.

With a SQL Server back end, using the same method is slow because the data
is actually sent from Access to SQL Server one row at a time.

I know that there are several methods of inserting the data from Access to
SQL Server, without using DTS/SSIS?

Can anyone tell me which is the fastest?

[David W. Fenton]

Post by agiamb
With a SQL Server back end, using the same method is slow because
the data is actually sent from Access to SQL Server one row at a
time.
I know that there are several methods of inserting the data from
Access to SQL Server, without using DTS/SSIS?

One method might be to use a passthrough and use the IN clause in
the FROM like this:

INSERT INTO ...
SELECT ...
FROM tblSource IN '\\Server\PathToDatabase\TempTable.mdb'

Note that you can't use a file on the C: drive of your local
computer because it will not be accessible to the SQL Server (unless
you share it, of course), so as above, you'd want to do your import
from a temp database stored on a server that is accessible to the
SQL Server.

There may be some variations on the SQL Server vocabulary for this,
though. I'm only familiar with doing it in plain old Access.

[agiamb]

Thanks David.
Nice idea, I could create a temp accdb on the server and name it with the
user's name for isolation, link it to my app, do the initial import into
that, massage the data with user input, then do the import right from SQL
Server. There is a stored procedure that runs after the transfer to SQL, so
I can just add the import to that.
--
AG
Email: npATadhdataDOTcom

Post by David W. Fenton

Post by agiamb
With a SQL Server back end, using the same method is slow because
the data is actually sent from Access to SQL Server one row at a
time.
I know that there are several methods of inserting the data from
Access to SQL Server, without using DTS/SSIS?

One method might be to use a passthrough and use the IN clause in
INSERT INTO ...
SELECT ...
FROM tblSource IN '\\Server\PathToDatabase\TempTable.mdb'
Note that you can't use a file on the C: drive of your local
computer because it will not be accessible to the SQL Server (unless
you share it, of course), so as above, you'd want to do your import
from a temp database stored on a server that is accessible to the
SQL Server.
There may be some variations on the SQL Server vocabulary for this,
though. I'm only familiar with doing it in plain old Access.
--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

[David W. Fenton]

Post by agiamb
Nice idea, I could create a temp accdb on the server and name it
with the user's name for isolation, link it to my app, do the
initial import into that, massage the data with user input, then
do the import right from SQL Server. There is a stored procedure
that runs after the transfer to SQL, so I can just add the import
to that.

You'll probably need a full connect string (I'd suggest OLEDB),
unless you permanently use "link server" to mount the temp files for
use in the SQL Server.

[agiamb]

Thanks David, I'm pretty sure I already have a sample of that.
--
AG
Email: npATadhdataDOTcom

Post by David W. Fenton

Post by agiamb
Nice idea, I could create a temp accdb on the server and name it
with the user's name for isolation, link it to my app, do the
initial import into that, massage the data with user input, then
do the import right from SQL Server. There is a stored procedure
that runs after the transfer to SQL, so I can just add the import
to that.

You'll probably need a full connect string (I'd suggest OLEDB),
unless you permanently use "link server" to mount the temp files for
use in the SQL Server.
--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

[lyle fairfield]

Post by agiamb
My application uses ODBC connected tables to SQL Server 2008 R2 Express as
the back end, across a LAN.
One function is to import data from Excel files on the local machine,
perform some manipulation on the imported data and add some user input.
With an Access back end, I typically would  import the Excel data into a
temporary local table, do the processing, then use an append query to insert
it into the live back end table.
With a SQL Server back end, using the same method is slow because the data
is actually sent from Access to SQL Server one row at a time.
I know that there are several methods of inserting the data from Access to
SQL Server, without using DTS/SSIS?
Can anyone tell me which is the fastest?
--
AG
Email: npATadhdataDOTcom

You could attach the Excel file as a linked server and then process
everything on the SQL server as the Excel "tables' will be available
at that level.

Creating the linked server can be done fairly easily using a utility
such as SQL Server Managment Studio (free) or you can just create a
procedure in Access to do so, eg:
CREATE Procedure [dbo].[LinkToOPEExcelFile]
@FileLocation nvarchar(255)
AS
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id !=
0 AND srv.name = N'OPE_XLS') EXEC master.dbo.sp_dropserver
@server=N'OPE_XLS', @droplogins='droplogins'
EXEC master.dbo.sp_addlinkedserver @server = N'OPE_XLS',
@srvproduct=N'Excel', @provider=N'Microsoft.Jet.OLEDB.4.0',
@datasrc=@FileLocation, @provstr=N'Excel 8.0'

With a linked server one must/should? use fully qualified references:
Server Name.Database Name. Owner Name.Object Name. My experience with
this is "usually".

Transferring Data from a linked server is very fast; adding a few
scalar functions may allow the manipulation of the data completely
independently of Access and thus appear to be instantaneous.

A potential problem with this solution is that the Server SA might not
be enthusiastic about linked servers and the permissions he/she has
granted you may not allow them.

On the other hand if you're dealing with huge gobs of data, using some
form of bulk import (SSIS, which you've implied you don't want to use)
may be fastest of all.

[agiamb]

Thanks for the reply Lyle.
While I am sure it is not impossible, I don't see linked server being
practical in the client's environment, with the Ececl file on the user's
machine.
--
AG
Email: npATadhdataDOTcom

Post by agiamb
My application uses ODBC connected tables to SQL Server 2008 R2 Express as
the back end, across a LAN.
One function is to import data from Excel files on the local machine,
perform some manipulation on the imported data and add some user input.
With an Access back end, I typically would import the Excel data into a
temporary local table, do the processing, then use an append query to insert
it into the live back end table.
With a SQL Server back end, using the same method is slow because the data
is actually sent from Access to SQL Server one row at a time.
I know that there are several methods of inserting the data from Access to
SQL Server, without using DTS/SSIS?
Can anyone tell me which is the fastest?
--
AG
Email: npATadhdataDOTcom

You could attach the Excel file as a linked server and then process
everything on the SQL server as the Excel "tables' will be available
at that level.

Creating the linked server can be done fairly easily using a utility
such as SQL Server Managment Studio (free) or you can just create a
procedure in Access to do so, eg:
CREATE Procedure [dbo].[LinkToOPEExcelFile]
@FileLocation nvarchar(255)
AS
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id !=
0 AND srv.name = N'OPE_XLS') EXEC master.dbo.sp_dropserver
@server=N'OPE_XLS', @droplogins='droplogins'
EXEC master.dbo.sp_addlinkedserver @server = N'OPE_XLS',
@srvproduct=N'Excel', @provider=N'Microsoft.Jet.OLEDB.4.0',
@datasrc=@FileLocation, @provstr=N'Excel 8.0'

With a linked server one must/should? use fully qualified references:
Server Name.Database Name. Owner Name.Object Name. My experience with
this is "usually".

Transferring Data from a linked server is very fast; adding a few
scalar functions may allow the manipulation of the data completely
independently of Access and thus appear to be instantaneous.

A potential problem with this solution is that the Server SA might not
be enthusiastic about linked servers and the permissions he/she has
granted you may not allow them.

On the other hand if you're dealing with huge gobs of data, using some
form of bulk import (SSIS, which you've implied you don't want to use)
may be fastest of all.

[Chuck Grimsby]

Post by agiamb
My application uses ODBC connected tables to SQL Server 2008 R2 Express as
the back end, across a LAN.
One function is to import data from Excel files on the local machine,
perform some manipulation on the imported data and add some user input.
With an Access back end, I typically would  import the Excel data into a
temporary local table, do the processing, then use an append query to insert
it into the live back end table.
With a SQL Server back end, using the same method is slow because the data
is actually sent from Access to SQL Server one row at a time.
I know that there are several methods of inserting the data from Access to
SQL Server, without using DTS/SSIS?
Can anyone tell me which is the fastest?

I'm not sure what you mean by "the data is sent from Access to the SQL
Server one row at a time". That's the way *all* imports are done,
depending upon how close you want to look at how SQL Server processes
items. A "bulk" insert (INSERT * INTO MyTable type operation in
Access) is another "fast" way to do things.

If the data manipulation you're doing can be automated, you may want
to upload to a "import" table and have a SQL Stored procedure do the
manipulations, the imports, and the clearing of the table after it's
done. That can often (but not always) faster then doing it in Access
or VBA. (Of course, that statement depends upon how good a coder your
DBA and your Access programmer are!)

You may also want to consider upgrading from the free "Express"
version to one of the more "robust" versions of MS SQL Server. There
are *lots* of advantages to the upgrades, beyond just DTS/SSIS....

[David W. Fenton]

Post by Chuck Grimsby

Post by agiamb
My application uses ODBC connected tables to SQL Server 2008 R2
Express a

s

Post by agiamb
the back end, across a LAN.
One function is to import data from Excel files on the local
machine, perform some manipulation on the imported data and add
some user input. With an Access back end, I typically would
 import the Excel data into

a

Post by agiamb
temporary local table, do the processing, then use an append
query to ins

ert

Post by agiamb
it into the live back end table.
With a SQL Server back end, using the same method is slow because
the dat

a

Post by agiamb
is actually sent from Access to SQL Server one row at a time.
I know that there are several methods of inserting the data from
Access t

o

Post by agiamb
SQL Server, without using DTS/SSIS?
Can anyone tell me which is the fastest?

I'm not sure what you mean by "the data is sent from Access to the
SQL Server one row at a time".

I think what he means is that instead of a single SQL INSERT being
executed, Jet "optimizes" the operation so that there's a SQL INSERT
to the SQL Server for every row of the source table.

Jet is being very careful and cautious to be a "good citizen" among
the users of the SQL Server, as this allows the server to serialize
and prioritize the data of all users, so that a bulk insert doesn't
tie up the server so that nobody can use it.

It's almost always completely unwarranted, so we always have to work
around it in a way that bypasses Jet's "optimization."

Post by Chuck Grimsby
That's the way *all* imports are done,
depending upon how close you want to look at how SQL Server
processes items. A "bulk" insert (INSERT * INTO MyTable type
operation in Access) is another "fast" way to do things.

With ODBC linked tables, what the SQL Server gets from Jet is one
INSERT for every row. Turn on SQL Profiler and watch.

Post by Chuck Grimsby
If the data manipulation you're doing can be automated, you may
want to upload to a "import" table and have a SQL Stored procedure
do the manipulations, the imports, and the clearing of the table
after it's done. That can often (but not always) faster then
doing it in Access or VBA. (Of course, that statement depends
upon how good a coder your DBA and your Access programmer are!)

How does "upload to an 'import' table" bypass Jet's "optimization"
of a bulk INSERT into single-row inserts?

Post by Chuck Grimsby
You may also want to consider upgrading from the free "Express"
version to one of the more "robust" versions of MS SQL Server.
There are *lots* of advantages to the upgrades, beyond just
DTS/SSIS....

Specifically? I can't see any, myself. And isn't DTS deprecated?

[agiamb]

David,

Thanks for a good explanation. You hit the nail on the head.
Yes, DTS is depricated since SQL Server 2005. I threw it in because it is
sometimes the term is used interchangably with SSIS.

You are also correct in that there are no real advantages in going to the
paid version, at least for now. Once I get the system in production, if
client has performance problems, he may need to upgrade in order to take
advantage of the additional processor and memory features. Other than that,
it is the same engine.
--
AG
Email: npATadhdataDOTcom

Post by David W. Fenton

Post by Chuck Grimsby

Post by agiamb
My application uses ODBC connected tables to SQL Server 2008 R2
Express a

s

Post by agiamb
the back end, across a LAN.
One function is to import data from Excel files on the local
machine, perform some manipulation on the imported data and add
some user input. With an Access back end, I typically would
import the Excel data into

a

Post by agiamb
temporary local table, do the processing, then use an append
query to ins

ert

Post by agiamb
it into the live back end table.
With a SQL Server back end, using the same method is slow because
the dat

a

Post by agiamb
is actually sent from Access to SQL Server one row at a time.
I know that there are several methods of inserting the data from
Access t

o

Post by agiamb
SQL Server, without using DTS/SSIS?
Can anyone tell me which is the fastest?

I'm not sure what you mean by "the data is sent from Access to the
SQL Server one row at a time".

I think what he means is that instead of a single SQL INSERT being
executed, Jet "optimizes" the operation so that there's a SQL INSERT
to the SQL Server for every row of the source table.
Jet is being very careful and cautious to be a "good citizen" among
the users of the SQL Server, as this allows the server to serialize
and prioritize the data of all users, so that a bulk insert doesn't
tie up the server so that nobody can use it.
It's almost always completely unwarranted, so we always have to work
around it in a way that bypasses Jet's "optimization."

Post by Chuck Grimsby
That's the way *all* imports are done,
depending upon how close you want to look at how SQL Server
processes items. A "bulk" insert (INSERT * INTO MyTable type
operation in Access) is another "fast" way to do things.

With ODBC linked tables, what the SQL Server gets from Jet is one
INSERT for every row. Turn on SQL Profiler and watch.

Post by Chuck Grimsby
If the data manipulation you're doing can be automated, you may
want to upload to a "import" table and have a SQL Stored procedure
do the manipulations, the imports, and the clearing of the table
after it's done. That can often (but not always) faster then
doing it in Access or VBA. (Of course, that statement depends
upon how good a coder your DBA and your Access programmer are!)

How does "upload to an 'import' table" bypass Jet's "optimization"
of a bulk INSERT into single-row inserts?

Post by Chuck Grimsby
You may also want to consider upgrading from the free "Express"
version to one of the more "robust" versions of MS SQL Server.
There are *lots* of advantages to the upgrades, beyond just
DTS/SSIS....

Specifically? I can't see any, myself. And isn't DTS deprecated?
--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

[Mack Jon]

I know that I am replying on old post, but this post helped me a lot to understand the case. After that I used third party application that helped me in migration. So, I am sharing official page link here, it can help users to reach right solution easily.

https://www.systoolsgroup.com/access/sql/