Compare two database tables of differing types and compare their data?

Question

I have two database tables from 2 different databases, one in MYSQL and one in MSSQL. Both have similar data, and one is somewhat based on data from another (Only some columns in common). They're in two different databases because one is a remote system used for Crystal Reports (MSSQL) and another is used to run our Workflow Management System (MYSQL).

MSSQL Database Table - Job_Operation

Job_OperationKey | Job_Operation | Vendor | Work_Center | Job | Etc...

MYSQL Database Table - View_Job_Info

Id | job_num | posted_date | columns etc.. | latest_workorder_date |

The best comparison that can be done that I can see is between Job and job_num. This way, if Job #### matches job_num #### then tabulate columns from both tables into 1 table. I'm not sure how plausible this is because I've ran into memory issues querying everything from Job_Operation when using Adminer.

The thing is, I don't need every single job, I just need jobs that match up to this particular query:

Select * 
    FROM view_job_info 
    WHERE (DATEDIFF(NOW(), latest_workorder_date) < 90 OR (DATEDIFF(NOW(), posted_date) < 30)) 
        AND job_num > 2000`

This way I can get the current active jobs.

How can this be made possible?

Why not connect to the MYSQL database with Crystal Reports, and join the two tables together in there? — Oliver Lockett
– Oliver Lockett, Commented Jan 31, 2014 at 13:21
@mikeoscarecho what do you mean by "tablulate columns from both tables into 1 table" and is that your only requirement? — Punter015
– Punter015, Commented Jan 31, 2014 at 13:24
@OliverLockett I'm on a Linux machine but I didn't even know that was possible. If I do that some how I would still have to merge the data somehow, check both Job and job_num columns and if both match then store them in a table? Can I query Crystal using PHP to output data to a page? — MikeOscarEcho
– MikeOscarEcho, Commented Jan 31, 2014 at 13:26
there are a couple of solutions here:stackoverflow.com/questions/16220490/… but I would probably set up a job to copy the SQL server job table to the mysql database once a week, and then you can query everything from there. Speak to your dba - he'll be able to set that up easy — Oliver Lockett
– Oliver Lockett, Commented Jan 31, 2014 at 13:49
@MikeOscarEcho may be you can query data in MySQL from MSSQL (Like linked_server in MSSQL) and create a view in MySQL joining both the tables to display your wanted result. That would be a start. — Punter015
– Punter015, Commented Jan 31, 2014 at 13:57

Punter015 · Accepted Answer · 2014-01-31 14:34:11Z

2

Create a linked server from MSSQL to MySQL.
Access the MySQL table in MSSQL -- Ex. select * from openquery(test,'select * from MySQL_Table) , Test is the linked server name and MySQL_Table is the table in MySQL
Put the output of the above query into a temporary table.
Inner join the temporary table with the MSSQL Table on job_num and job.
output of the result of the above can be used in any way you want.

more over contact your database administor to perform the above activities. refer this http://www.packtpub.com/article/mysql-linked-server-on-sql-server-2008 to create the linked server in MSSQL.

answered Jan 31, 2014 at 14:34

Punter015

1,8541 gold badge11 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MikeOscarEcho Over a year ago

Thanks for the step by step, I'll get working on it and report back if I need more clarification on something.

Collectives™ on Stack Overflow

Compare two database tables of differing types and compare their data?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related