X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Importing Csv To SQL Via Access, SQL Server 2012    
post Sep 11 2017, 10:59 AM

Posts: 916
Joined: 27-July 05

Hi all,

I receive a daily CSV export out of our information system for reporting purposes. Because importing this into SQL via Access/ODBC connection is so slow, I have been MANUALLY running a BULK INSERT query in SQL Server.

I was hoping to automate this process, which I believe I can do. I just don't know if there is an efficient or fast way to import this data from Access into SQL Server and have it not take so long. The data file is pretty big, but when I do it via BULK Insert in SSMS, it is pretty fast.

Any ideas on how I can run something similar to a bulk insert statement from Access so that I can automate this process?

Go to the top of the page
post Sep 11 2017, 12:09 PM

UA Admin
Posts: 30,470
Joined: 20-June 02
From: Newcastle, WA

It's been a while since I did this, but the general idea would be to set up a job in SQL Server to o the bulk insert, and a stored proc to run that job as needed.

Then you can call the stored proc in a pass-thru query in Access.

Go to the top of the page
post Sep 11 2017, 12:54 PM

UtterAccess VIP
Posts: 2,514
Joined: 12-April 07
From: Edmonton, Alberta Canada

I suggest you try using a recordset.

Assuming you were importing the CSV table into Access, and then doing a append query?

You could do a test with a recordset – they often are faster in this case.

Quite sure SSMS would be the fastest, but above could be a “middle” compromise in terms of ease of use.

Another approach would be to have Access “copy” the csv file to a location that resides on the SQL server machine. You then execute a pass-through command to import that CSV.

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
Go to the top of the page
post Sep 11 2017, 02:40 PM

Posts: 916
Joined: 27-July 05

Hi Albert,

Do you have an example of doing this with a recordset? Just need somewhere to start.

Go to the top of the page
post Sep 18 2017, 04:13 PM

Posts: 2,157
Joined: 27-February 09

Why not use BULK INSERT statement to do it, or use SSIS and create a job for it and run it in the middle of the night?
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    24th September 2017 - 09:05 AM