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

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?

Answers


  1. Create a linked server from MSSQL to MySQL.
  2. 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
  3. Put the output of the above query into a temporary table.
  4. Inner join the temporary table with the MSSQL Table on job_num and job.
  5. 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.


Need Your Help

Ignore triggers when gathering dependencies with SMO

c# sql sql-server sql-server-2008-r2 smo

I have a system that transfers data between two databases with identical schemas (for development purposes).

Bootstrap Navbar toogle not working correctly

jquery html css wordpress twitter-bootstrap

I am using Bootstrap in my WordPress theme to make it responsive.