Importing Access Database table data using Stored Procedure

Question

Post reply

Importing Access Database table data using Stored Procedure

mramey

SSC Veteran

Points: 266
More actions
July 30, 2012 at 11:35 am

#259935

Hi,
I have an application that needs to import data from an Access database. The table can be anywhere from a few hundred to over a million records. I can import the data using the Import wizard in SS but I need it to import the data when a user prompts it.
I can pass in the path and database/table name to the stored procedure. After that I need the SP to connect to the Access database and Insert the data from it into the SQL Server table. As all of you that work with access know that it is very sluggish when handling data when the record count is as high as it can get with mine. The access database in which I'm pulling my data is created by another program my users use to process their work. I have no control over the structure or data limits. I just need the data in it.
Can anyone instruct me on how to make this happen in SS?
Thanks
Mike

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Will the structure of the Access table be the same every time?

Will the table names be the same every time?

Will the Access database name be the same every time?

Or can any/all of these things change in the Access database that needs imported?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Lowell SSC Guru Points: 323504 More actions · Answer 2

on a similar note, has anyone gotten a linked server or openrowset command to work on 64 bit / ACE drivers?

I see there is a connect item on it with 5 work arounds:

http://connect.microsoft.com/SQLServer/feedback/details/587897/connecting-via-a-linked-server-to-an-access-2010-database-file

i spent ten minutes trying to make a linked server with no luck so far.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Which version of ACE drivers?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Lowell SSC Guru Points: 323504 More actions · Answer 4

SQLRNNR (7/30/2012)
Which version of ACE drivers?

for me, I was trying to use ACE 12.0 drivers, which i know i installed, and can use for Excel xlsx files via openrowset so far.

my non-working code example for a linked server was like this:

DECLARE @server sysname,

@srvproduct nvarchar(256),

@provider nvarchar(256),

@datasrc nvarchar(100),

@location nvarchar(100),

@provstr nvarchar(100),

@catalog sysname,

@sql varchar(1000)

--add an access Database as a linked server

SET @server = N'AccessDb64'

SET @srvproduct = N'Access'

SET @provider = N'Microsoft Office 12.0 Access Database Engine OLE DB Provider'

SET @datasrc = N'C:\data\mydatabase_Copy.mdb'

set @provstr = N'Microsoft.ACE.OLEDB.12.0'

EXEC sp_addlinkedserver @server,@srvproduct,@provider,@datasrc,NULL,@provstr

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mramey SSC Veteran Points: 266 More actions · Answer 5

Yes the structures are always the same. As I said before I will pass the path and names to the SP from an access vba application. I have accomplished the data import using vba but it takes over 5 mins to move just 97,000 records. I don't even want to try a million.

Lowell SSC Guru Points: 323504 More actions · Answer 6

mramey (7/30/2012)
Yes the structures are always the same. As I said before I will pass the path and names to the SP from an access vba application. I have accomplished the data import using vba but it takes over 5 mins to move just 97,000 records. I don't even want to try a million.

oof yeah that's a bit slow.

I think i'd end up doing this in a programming language or SSIS: connect to access and fill a local datatable, and then use a SQLBulkCopy to insert into the server.. but is it pure insert or do you have to merge?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

Lowell (7/30/2012)
mramey (7/30/2012)
Yes the structures are always the same. As I said before I will pass the path and names to the SP from an access vba application. I have accomplished the data import using vba but it takes over 5 mins to move just 97,000 records. I don't even want to try a million.
oof yeah that's a bit slow.
I think i'd end up doing this in a programming language or SSIS: connect to access and fill a local datatable, and then use a SQLBulkCopy to insert into the server.. but is it pure insert or do you have to merge?

In this case, I am in favor of SSIS as well.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events