2

I have a SQL server DB, a Oracle DB, a MySQL DB. I have a way to join the tables from each DB. How would be the best way to create this view? I have just started SSIS project that pulls the tables from the DBs and creates the view I want, but is there a way to grab the data from each engine in the view in real-time\dynamic?

Improve this question
7
  • I'd guess step one would be to put them all on a single database server. Commented Feb 8, 2019 at 1:55
  • Load the data from other Db's to one db and create the view. If you use linked server it has other issues until unless data is very small. Commented Feb 8, 2019 at 2:00
  • @Ed that's possible but that would be a massive project. Commented Feb 8, 2019 at 2:00
  • @Roshan I have the Oracle linked to the MSSQL already. I'll look at linking the MySQL tomorrow. Thanks! Commented Feb 8, 2019 at 2:05
  • Having thought about it a bit more, you could probably do it with an SQL proxy server. I've not looked into the capabilities of SQL proxy servers; you would need one that could connect to all of the servers that couldn't already talk with each other. Commented Feb 8, 2019 at 2:07

1 Answer 1

Reset to default
2

Thr best way to do that is to create a linked server for each instance (Oracle, MySQL) on sql server without the need to copy data.

And then you can create a view that join the tables between instances.

For more information, you can follow these articles:

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.