Best method to import access data into SQL automatically

Posted on 2007-11-27
Last Modified: 2010-04-21
I have a need to import/replace data automatically (hands off) several times a day into a live SQL server.

I have legacy data in both MS Access and Paradox DBF formats that has to be imported into SQL Server 3 times each day to keep the inventory "current". It needs to replace the data or at least update it where it is different.

I need it to be fast so it doesn't disrupt the websites that use the data.
 The way I am doing it right now is bound to give me trouble.

So far the best method I could come up with is a vbscript VBS file that deletes the current SQL Table data and then repopulates the same SQL Table from the msdb or dbf.  
I am fairly certain that is an inefficient way to go.

Ideally I need it to just update existing data and add new data, but I have no clue how to do that.
Scripting that in vbscript would probably take too many resources and cause many problems.

Any suggestions?

Question by:EGormly
  • 5
  • 3
  • 2

Expert Comment

ID: 20359326
I would create an 'import table'  loading your data from the two other databases, and then run update queries from sql.  I would even go so far as to write the SQL piece as a stored procedure, then write an application to run the stored procedure on a periodic basis.  In this manner the speed of updates will be preserved...

Author Comment

ID: 20360214
Do you mean:

SQL Server Tables:

Import Data from Access Database into TempDataMDB Table
Import Data from DBF Database into TempDataTableDBF
Use an Update querie to take data from the

and put into


Delete Data from Access Database into TempDataMDB Table
Delete Data from DBF Database into TempDataTableDBF

Thanks, but although that does preserve the speed, it does nothing about my need for a "How"
I am pretty much clueless on SQL and storing queries or the like.  
I am fairly decent at VBScript but where do I start?

I wouldn't know how to write an update query from one table to another
preserving all the intracasies of the data.  Should I still use VbScript to do the iomporting into the SQL temp tables?  I am still a bit lost on this.

Accepted Solution

the_b1ackfox earned 500 total points
ID: 20363684
yes exactly...  as for the how, how much of the proposed solution do you already know how to do?  1) Import the data  2) Creat an update query 3) turn the query into a SP 4) clear the temp tables  5) write an app to run the SP

From your previous post, I think you are good on 1, 4 & 5
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.


Author Comment

ID: 20365857
I have found the answers to most of my problem in the last few hours.
I learned how to create a stored procedure, learned how to move identical data from one table to another and learned how to execute it.

I only need to learn how to create a stored procedure that will import access data into an existing table.

So it's number 1 actually.

How to import Access data into an existing SQL Server Table

Expert Comment

ID: 20369499
Take a look at this to see if might be of any help.

Managing Data with Data Transformation Services

Expert Comment

ID: 20373666
Interesting...  So you're going to do exactly as I recommended but not give any pts.  To whom ever is moderating, I object.  It would be 1 thing if the solution was off base.  In this case the person asking the question has taken the advice.  I pity anybody whomever ends up with people like this as clients

Author Closing Comment

ID: 31411220
I didn't mean to annoy you with a request for cancellation.

I felt my question wasn't answered the way I wanted it to be.
perhaps that was my fault for not asking the correct question to begin with.

Before I  posted the question I was already using scripts to import the data (vbscript) into the live database.

You added that I should do it in a temp database.
While that was not a complete solution or a complete answer to my question, it should still qualify as points as it is a help.

I will re ask the remainder of my question in another post.

Expert Comment

ID: 20381186
I object as well for the same reasons that b1ackfox is stating. He responsed on more then one occasion to Egormly questions and guided him in the direction needed. Points should be rewarded to b1ackfox.

Author Comment

ID: 20381379
MeCanHelp .. way to go on commenting where not needed.
I awarded him the points before you posted that.. no need to add to a discussion once it is settled.

Author Comment

ID: 20381472
I have to clarify something...

I made a request to close this question in the lounge.
It was a mistake, I meant to ask to close 22985749.
This one needed to stay open so I could get the answer I needed.

When I saw the comment by the_b1ackfox about my decision to ask for closure I was annoyed at him without really realizing I asked to close the wrong question.

Compunding the problem.

I was a bit confused and then after re-reading the question made a snap comment about it not being complete. It wasn't complete but I should have instead just told him that I wasn't yet finished with the queston, I am sure the_b1ackfox would have answered the remainder of the question, but because of my successive errors... this is where we are.

the_b1ackfox: I apologize for the misunderstanding.

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Help with simplifying SQL 6 53
Problem to With line 4 57
SQL query with cast 38 42
excel 2016 program to loop through scripts 6 35
This script will sweep a range of IP addresses (class c only, and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
In a recent question ( here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question