Solved

Best method to import access data into SQL automatically

Posted on 2007-11-27
11
181 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
11 Comments
 
LVL 9

Expert Comment

by:the_b1ackfox
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:EGormly
Comment Utility
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
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 9

Expert Comment

by:the_b1ackfox
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
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…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now