I don't use WooCommerce so I'm not familiar with the database structure. However, I'll explain how I synchronize inventory between RMS and my online store so hopefully you can take it from there.
You can either sync data by connecting directly to the mysql database as a linked server or you can export the inventory data into a file that you FTP to your server and then use to update mysql.
You'll need to have database fields in each database which can be used to link items. item.itemlookupcode in RMS is the most logical and hopefully there is a field in WooCommerce in which you can add itemlookupcodes.
Linked server method:
1. create linked server:
www.ideaexcursion.com/.../howto-setup-sql-server-linked-server-to-mysql
www.packtpub.com/.../mysql-linked-server-sql-server-2008
2. use a query like this in SQL Server Management Studio:
UPDATE w
SET w.TempeQty = i.Quantity
FROM OPENQUERY([WEBSHOP], 'SELECT itemlookupcode, quantity_field FROM mysql_database.inventory_table') w
INNER JOIN [RMSdatabase].[dbo].[Item] i WITH (NOLOCK) ON w.itemlookupcode = i.itemlookupcode
WHERE w.TempeQty <> i.Quantity;
3. once you confirm that it works you can create a .bat file to run the query via OSQL or sqlcmd and then set it up as a scheduled task. Here is an example of the contents of the .bat file:
osql -S 172.168.1.102\SQLExpress -dRMSdatabase -Usa -Ppassword < file_containing_sql_query.sql
Data export and FTP method:
1. create a .bat file with a bcp command and FTP command. For example:
bcp "SELECT itemlookupcode, quantity FROM RMSdatabase" queryout c:\inventory_for_mysql.txt -c -k -t, -S 172.168.1.102\SQLExpress -dRMSdatabase -Usa -Ppassword
ncftpput -C -u ftp_user -p ftp_password domain_name c:\inventory_for_mysql.txt c:\inventory_for_mysql.txt
I use ncftp (http://www.ncftp.com/ncftp/) but you can use the ftp command built into Windows. If you have a lot of items and the file is big you can compress it using a 7zip command in the .bat file. Here is an example of what I use:
cd "C:\program files\7-zip"
7z a -tzip "c:\inventory_for_mysql.zip" "c:\inventory_for_mysql.txt"
2. create a new table in mysql that serves as a temporary place to load the inventory data before it's used to update the WooCommerce inventory table
3. Create a shell script to import the data. If using a Linux server here is an example:
#!/bin/sh
mysql temp_database -u mysql_username -ppassword < /path/to/sql/script/inventory_data_import.sql
contents of inventory_data_import.sql:
DELETE FROM temp_database.inventory_table;
LOAD DATA LOCAL INFILE '/path/to/file/inventory_for_mysql.txt' REPLACE INTO TABLE temp_database.inventory_table FIELDS TERMINATED BY '||' LINES TERMINATED BY '\r\n';
Create a cron job for the shell script.
Hopefully this will guide you in the right direction.
Lance
