Solved

Best method to import access data into SQL automatically

Posted on 2007-11-27
11
215 Views
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?




0
Comment
Question by:EGormly
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
11 Comments
 
LVL 9

Expert Comment

by:the_b1ackfox
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...
0
 

Author Comment

by:EGormly
ID: 20360214
Do you mean:

SQL Server Tables:
>TempDataTableMDB
>TempDataTableDBF
>LiveDataTableMDB
>LiveDataTableDBF

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

and put into

>LiveDataTableMDB
>LiveDataTableDBF

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.
0
 
LVL 9

Accepted Solution

by:
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
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:EGormly
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
0
 
LVL 4

Expert Comment

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

Managing Data with Data Transformation Services
http://www.microsoft.com/technet/community/events/sql2000/tnt1-78.mspx
0
 
LVL 9

Expert Comment

by:the_b1ackfox
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
0
 

Author Closing Comment

by:EGormly
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.
0
 
LVL 4

Expert Comment

by:MeCanHelp
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.
0
 

Author Comment

by:EGormly
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.
0
 

Author Comment

by:EGormly
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.
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

737 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