Jignesh Raiyani
DELETE MySQL Statement(TSQL)

MySQL Query (T-SQL) Tutorial for SQL Server

September 25, 2019 by

Microsoft SQL Server and MySQL, both are quite well-established database engines. In our role as a DBA, at some given time point we require T-SQL straddling between both database engines unites Select, Insert, Update, Delete and some other…

In terms of requirements for correlating with a MySQL database, eventually, we need to get to information from a MySQL database and synchronize it into Microsoft SQL Server. Now, a simple way to pursue that is with the utilization of MySQL connectors. The SQL Server statements or a query to insert, fetch or to do manipulations with the data from MySQL tables which are concerning to SQL Server tables. This means by inserting, fetching or manipulating in the solution we will have enough to import data from a MySQL Server database.

Now before dealing with T-SQL for MySQL inside the SQL Server, the Connection needs to be set up within inter-related Server as an initial step to start with. Additionally, the MySQL ODBC driver needs to be preinstalled on the machine.

Create a MySQL Linked Server

Let’s start with establishing an ODBC data source to the MySQL Server on the machine on which the SQL Server is installed.

Open ODBC Data Sources

To add new source, click the Add button, select the MySQL ODBC 8.0 ANSI Driver to integrate MySQL connection and click the Finish button.

Adding ODBC Connection where SQL SERVER is installed

Subsequently, configure the required information in MySQL Connector window to configure MySQL Data Source Name. In this example, we have used the localhost as the MySQL server name. Now, we can verify the connection with tapping on Test button. upon the successful test, the data source would be available to include another MySQL Linked Server in SQL Server illustration.

Configuring MySQL as ODBC Data Source

We didn’t select any database on the ODBC connection, but it works with the default database to execute T-SQL query over the connection. If you do not have mentioned database, then use databasename.tablename in T-SQL query to redirect your T-SQL statement on that particular database.

With the help of the Microsoft OLEDB Provider for ODBC Drivers with vital security refinements, we’ll create the link between servers with the final goal line of MySQL like the username (login) and the password for Connection Authentication.

Adding MySQL Linked Server for TSQL

Now when MySQL_LOCAL linked server is created let’s test the link between servers, to see whether it has been created in the correct manner or not.

Testing a connection for a Linked Server

The linked server is configured successfully to get the access to the MySQL_Local Server in SQL Server. But, make sure to allow Data access and RPC parameters to it in the configuration. Now we can start using the T-SQL assignment. Why MySQL is query execution essential in the Microsoft SQL Server? How could it be used and under what circumstances it can be used?

Using T-SQL query to fetch the MySQL table data in SQL Server

Microsoft SQL Server and MySQL both are very diverse by choice and not by the behavior. In most cases, the reason for using MySQL within the SQL Server is that software products are using both databases and need to manage the data in both the server database SQL Server & MySQL. Or must be using MySQL table column reference inside SQL Server table.

In such cases of integration of the third-party application, we require to cross-database reference inside the parent database and it is quite possible to use references of the information with each other and in the real-time scenario to see some of the RDBMS concepts having cross-database technologies as well.

How to use T-SQL Querying with MySQL inside SQL Server

OPENQUERY a pass-through query to executes the specified query on the specified linked server which could be a data source itself. The OPENQUERY can be used to reference in the FROM clause of a query as if it is the name of the table name and can also be used as an indicator as to the target table of an INSERT, UPDATE, or DELETE statement with the subject to the abilities of the Data source provider. However, the query could return multiple results sets the OPENQUERY will return only the first one of them.

SELECT MySQL Statement(TSQL)

As seen, the MySQL T-SQL Query is inside the OPENQUERY and [MYSQL_LOCAL] is a MySQL Linked server that exists in the SQL Server. The data could be filtered by both the query side, which means we can apply the WHERE clause at the outer side OPENQUERY level as well as the inside query statement.

Insert data into MySQL using SQL Server

The question will arise of maintaining the data consistency when product is using both database servers database SQL Server & MySQL then most certainly we need to supervise the number of transactions based on the substantiality of the databases.

We will come across this type of real-time scenarios while we will be operative on Micro-service based platform, hybrid of Micro-service and monolithic structural design platform. Recent trends operational are to make a database in Micro-service based design hence, that would be quite wearisome or not easy to manage data consistency in multiple databases if it is only Microsoft SQL Server or amalgamation of databases technologies such as SQL Server, MySQL etc.

See here, we have various examples to insert data in MySQL from SQL Server using T-SQL statement.

INSERT MySQL Statement(TSQL)

The data will be inserted into the table with the help of the above query which is mentioned inside the OPENQUERY. If a table has an Auto incremented column then that status will be managed by MySQL only. In the above example, we have Auto Incremental column user_id in the users table of d_portal database and the remaining columns will be part of the INSERT statement.

Fundamentally, data can be inserted into the table by a couple of T-SQL statement methods, INSERT … VALUES and INSERT … SELECT. Either of that can be applied in this case as well. So, it is a better way to administer transaction consistency rather than individual data insertion by application for any such cases.

Update data in MySQL using SQL Server (T-SQL)

In such cases, it is possible to maintain record in SQL Server and MySQL both databases and hence, the information updates on the row, row should be updated in both databases by back-end or using any such mechanism or processes. See here, we used MySQL UPDATE statement in the T-SQL query inside the SQL Server itself.

The above T-SQL query will update the column is_active with 0 where user_id = 1 in the users table of d_portal database. This method should be utilized in all such scenarios only to keep in mind for the performance of a database query. It is not compulsory to use this mechanism when we need to maintain the Lookup tables or the master tables in both SQL Server and MySQL databases. In the case of the metadata tables, I would suggest going with the back-end update applications only with an individual query however making it sure that should be in a persistent way and maintaining the data consistency.

Delete data in MySQL using SQL Server

In the same way as the update statement, if the rows need to be deleted from both databases SQL Server and MySQL then the rows should be deleted from SQL Server using this approach, same as SELECT, INSERT and UPDATE statement, we can also use DELETE within the OPENQUERY statement. The query structure will be the same as the usual DELETE statement for SQL Server and OPENQUERY statement will be same as mentioned in the above case in the T-SQL query.

DELETE MySQL Statement(TSQL)

Note that in the above statement, the T-SQL query will delete a row in MySQL database where user_id = 1. For delete statement and update statement, the SELECT statement inside the OPENQUERY needs only those columns only which are mentioned in WHERE clause to perform DELETE or UPDATE.

These are basic MySQL query tutorials within SQL Server which will help us to manage some level of data consistency. Otherwise performance inconsistency issues could appear and which are not even easy to identify in cross databases such as – what part is getting stuck and why?

Jignesh Raiyani

Jignesh Raiyani

Jignesh has good experience in Database Solutions and Architecture, working with multiple customers on Database Design & Architecture, SQL Development, Administration, Query Optimization, Performance Tuning, HA and Disaster Recovery.

View all posts by Jignesh Raiyani
Jignesh Raiyani
194 Views