?
Solved

Best method to import access data into SQL automatically

Posted on 2007-11-27
11
Medium Priority
?
232 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
  • 5
  • 3
  • 2
10 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 2000 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

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

When it comes to writing scripts for a Client/Server computing environment it is essential to consider some way of enabling the authentication functionality within a script. This sort of consideration mainly comes into the picture when we are dealin…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Loops Section Overview

850 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