Joining MySQL and MS SQL database tables

[je2ect]

Hi,
this is a problem that has been bugging me for months.
I have searched the internet for a solution and the only answer that i have come up with is 'it cant be done'.

I have a data driven web based program, written in html and php and I want to have an automated task that updates an MySQL table with information from an MS SQL table. So in other words instead of updating one server and then another I want to update one server with some data from another database, based on the user input.

I have not been able to get this off the ground as I keep coming up against a brick wall when I try to find out how this may be done.

Now I know common sense says that I should first select the info from one database and use php to open a connection to the other database and then insert the data. That is exactly what i do not want to do.
what i would like to do is write ONE SQL query that will do it for me, without the need for additional scripting. In my minds eye I have an image of linking relational databases and using only SQL (not t-SQL or any other dialect) to query and update them from one program.
Before you go off at me I know about RDBMS, filemaker pro, ODBC/JDBC. OLE DB, SQL leveling and so on. It's just that it would be so much more simpler if I could access my relational databases from one program using just SQL commands.

I would appreciate any nudges in the right direction
links to academic papers
links to how it can be done
a total scathing remark

thank you

[wwb_99]

How about a scathing remark: if you want to access your database with one program using SQL commands, put the stuff in the same database. Or at least different databases on the same platform.

I think one could do some "interesting" things on Sql Server to make the MySql DB "appear" to be a database on that server, but that probably will fall flat at some point.

Now, insofar as automated tasks go, you could easily use SSIS to handle this stuff. But that is not even a dialect of SQL.

[je2ect]

could the reason it cant be done possibly be because of a lack of development with SQL and its relationship with the relational model? Do you know anything about how the language came to be structured as it is?

[r937]

a lack of development with SQL and its relationship with the relational model?

yeah, that sounds right

not



let me ask you a question in return... why do you have two different database platforms with the same data?

[je2ect]

The reason is that i have worked in so many small businesses that have to manually take information from columns in one database and insert it into another because they cannot automate the job, and the reason they cannot automate the job is because they would have to have another program written (possibly web based) to do it. Even then with another program, in most cases they couldnt even do the job manually because of opcodes.

I am just trying to find out (given no opcodes ) whether it is possible to write one query in SQL that selects data from columns in one database and inserts the extracted data to columns in a table on the other database - thats why I have two different databases.

Do you know the reason it cant be done? as you seem pretty certain its not a lack of development of the sql language "not"

I think it would be interesting to discover whether it is possible without the use of ODBC drivers and scripting. Despite a few weeks of trawling I cannot find out.

Thank you

[Dr John]

WHY do these businesses have separate databases???

Or are they trying to migrate some of the contents of one database to another one on to a public web site for others to use?

If it is the latter, try extracting the data you need then uploading it.

Deliberately keeping some data in one database and the rest in another is poor design by whoever built the databases. Or the client, not knowing what they were doing, asked for another database when they should have asked for the existing one to be improved - I've been asked a few times to "build another database", when in reality all the client needed was to add a new table or two to the existing one. It's possible your clients have asked for something they didn't need or used the wrong words, and been given what they asked for, and have generated their own problems.

[r937]

okay, let me ask you a question about this mythical SQL that you want to run

you want the SQL to select data from "columns in one database" and insert it "to columns in a table on the other database"

presumably you are thinking of the INSERT INTO ... SELECT FROM ... syntax?

i fail to understand how you want to set this up without someone writing the SQL for it, column by column

your main argument for not doing it in php is that the client "would have to have another program written"

so who's going to write the SQL?

and why isn't this the same as "having another program written"?

if the SQL is dead easy, then it sounds like the databases actually have duplicate structures and contents

if they don't, then the SQL isn't going to be so straightforward, is it?

could you explain again why would you not want to use php with two different connections?

[je2ect]

Ok, here' the deal,
One data base is set up in MySQL the other is MS SQL server 2005
MySQL has a table for prospective clients, works with dialler software and is updated by users when a prospective client takes up a service with the company.

The MS SQL Server 2005 holds the details of clients already on the company books and is updated by the user using another desktop program, data is also accessible to clients via a webpage

Allow me to point out at this stage that I know perfectly well how to write the php script and SQL syntax to SELECT from one and INSERT into the other, and about ODBC.

The problem was that when I was working for that company the database that held the existing clients details had proprietary file extensions and the IT company that owned the database would not allow the database to be accessed other than through the desktop program. We tried everything to get them to allow us to try to INSERT data from the database (dialler) but they were not for re-writing their own code and all sorts of other excuses. The only access that was allowed was a request for the data to be exported to an excel file.
Now, irrespective of the 'politics' of the situation this, and a few other, experience has led me to E. Codd (relational model) and SQL. I know that SQL has a declarative part and was not specified as the only language that could be used with relational databases. What I wonder is whether it would be breaking any for E. Codds rules ( specifically the rules about there being a single language to interact with relational databases and the way that language is to behave with relational databases) if the standard algorithm used by SQL were to include some form of imperative programming algorithms?

I guess it's back to the book for me and to have a good look at predicate logic and set theory and see if there is any connection between them and declarative and imperative programming.

I guess there is just no simple way of doing a simple job and somewhere along the line either the relational model didnt allow for 'how to', or, SQl didnt allow for 'how to' with relational databases/models (or allowed a a limited set of algorithms). Please note I am not against Open standards or the Common Application environment, I just would like a single language that can connect to other relational databases and carry out queries without the SQL/CLI and ODBC and everything else that is needed

[r937]

have fun stormin' the castle





bonus marks if you know who said that, in which film

[je2ect]

The Princess Bride (1987), and it was Miracle Max - said it at least twice lol

[wwb_99]

The problem is not the sql language here--it is how these things are executed. You pass SQL off against a database engine for parsing/planning/query caching. All of which is an internal binary operation to that database engine and which has no reason to be cross-platform compatible.

I understand the IT staff's position--I would not let some random contractor come in and start inserting data into my line of business systems without at least a long history with them. Especially if it s a proprietary package not meant to be touched by human hands.

What you probably want to do here is make a 3rd database--effectively your data warehouse. Then use an ETL tool (such as Sql Server Integration Services) to pull data from both databases into the data warehouse, then do whatever reporting and validation from there.

Off Topic:

What is an opcode and how does it have to do with any of this?