• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

MS SQL import script

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
Dan560
Asked:
Dan560
4 Solutions
 
Aaron TomoskyTechnology ConsultantCommented:
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
 
arnoldCommented:
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
 
ahoffmannCommented:
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
 
Ray PaseurCommented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now