Solved

MS SQL import script

Posted on 2013-01-25
4
183 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 79

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

This article discusses how to create an extensible mechanism for linked drop downs.
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

632 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