MySQL to SQL Server Import and automation
I have a Dedicated Server where our website and transactional database are stored. This server has a MySQL database.
I have a separate reporting server that I am going to use to import the data and using reporting tools on top of this.
I am seeking to do a few things:
1) Automate the creation of a table in the original MySQL database using a View. The purpose of doing this is to have a 'source' table with all the precalculated fields and necessary dimensions (OLAP dimensional structure) for easy reporting. I would like some advice on how to do this in MySQL. I currently have an SQL VIEW that takes 5 minutes to run - I plan on using a CREATE TABLE statement using this SELECT query to create a table. My issue here is that I don't want to everytime overwrite the table but rather re-import the data into the table. How can I schedule such a job to run once an hour? Please can you provide details on creating a table and then updating it subsequently without overwriting the table structure (indexes etc) with DDL.
2) Automate copying the table from the source (MySQL) database on the main server into the SQL Server 2012 database on the different reporting server. The methods I can think of are with very little MySQL or SQL Server experience:
I) FTP a .csv file from main server to reporting server and set up a schedule to import this in SQL Server.
II) Set up a direct connection from the SQL Server to MySQL database.
I would be grateful for a detailed answer with explanations of how the advised steps can be completed - e.g. where in application I need to look to do this.
I will give this job to a SQL Server expert.
Kind Regards,
Raheel
I have a separate reporting server that I am going to use to import the data and using reporting tools on top of this.
I am seeking to do a few things:
1) Automate the creation of a table in the original MySQL database using a View. The purpose of doing this is to have a 'source' table with all the precalculated fields and necessary dimensions (OLAP dimensional structure) for easy reporting. I would like some advice on how to do this in MySQL. I currently have an SQL VIEW that takes 5 minutes to run - I plan on using a CREATE TABLE statement using this SELECT query to create a table. My issue here is that I don't want to everytime overwrite the table but rather re-import the data into the table. How can I schedule such a job to run once an hour? Please can you provide details on creating a table and then updating it subsequently without overwriting the table structure (indexes etc) with DDL.
2) Automate copying the table from the source (MySQL) database on the main server into the SQL Server 2012 database on the different reporting server. The methods I can think of are with very little MySQL or SQL Server experience:
I) FTP a .csv file from main server to reporting server and set up a schedule to import this in SQL Server.
II) Set up a direct connection from the SQL Server to MySQL database.
I would be grateful for a detailed answer with explanations of how the advised steps can be completed - e.g. where in application I need to look to do this.
I will give this job to a SQL Server expert.
Kind Regards,
Raheel
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER