Solved

MS SQL import script

Posted on 2013-01-25
4
181 Views
Last Modified: 2013-01-31
Hi,

I was wondering if someone could assit me with this problem -

I have an ERP system that controls the stock levels of my products and want to update this and pricing information so that it matches my website's database.

From my server I have remote access to both databases. The ERP system uses MS SQL but my website uses MYSQL 5.0

My stock items will have the same ID in both databases.
 
I use the following SQL statement on my ERP system -

select
ID,price
from items
where status = 'a';

I basically want to update the results to the same fields on my websites database by scheduling a script to run on an hourly schedule.

The reason why I need help is because i've never really done this thing before with PHP and really not sure where to start!

Any help is appreciated!
0
Comment
Question by:Dan560
[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
4 Comments
 
LVL 39

Accepted Solution

by:
Aaron Tomosky earned 125 total points
ID: 38821390
start with adodb
http://adodb.sourceforge.net/

make connections to both databases
you can return an associative array of results, from the first, then insert/update them to the second.

Obviosuly it's more complex than this but thats the basic idea.
0
 
LVL 78

Assisted Solution

by:arnold
arnold earned 125 total points
ID: 38821416
Export data as CSV, the use bcp utility to import the data. Or the reverse not clear whether you have/maintain a reference.

Is PHP a requirement? Depending on where you are running the script, you may need to install freetds which provides the option for a Linux system to access ms sql.

The process would open a connection to each DB server and then perform a select from one and then insert/update not the other.
0
 
LVL 51

Assisted Solution

by:ahoffmann
ahoffmann earned 125 total points
ID: 38821880
I'd also suggest to avoid PHP for that
simply write two scripts: one for export mssql to a csv file ans one for importing the csv file into mysql, then schedule these scripts

from a security point of view: never ever connect the web server to your internal data, never ...
0
 
LVL 110

Assisted Solution

by:Ray Paseur
Ray Paseur earned 125 total points
ID: 38822399
Agree with the comments that suggest to avoid PHP.  It's not necessary, and you would add a long and steep learning curve for yourself if you're not already well-versed in PHP.

In unrelated news, if you want to learn PHP, this book is a pretty good place to start.
http://www.sitepoint.com/books/phpmysql5/
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

733 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