Skip to main content

How to Automatically Create SQL Server Views from MySQL Tables Using OPENQUERY (An alternative to ETL)

If you have a linked server from SQL Server to MySQL, you can automate importing data and creating views using dynamic SQL. This is useful when integrating external MySQL data into a Microsoft SQL Server reporting or analytics environment.

🔗 Setup: Linked Server to MySQL

Make sure you have already set up your MySQL linked server in SQL Server (for example, named SB), and that you can run queries like the following:

SELECT * FROM OPENQUERY(SB, 'SELECT * FROM your_table');

⚙️ Goal

We want to dynamically create SQL Server views for all base tables in a MySQL database, using a format like:

CREATE VIEW [dbo].[lnk_table_name] AS
SELECT * FROM OPENQUERY(SB, 'SELECT * FROM table_name WHERE deleted_at IS NULL');

But not all MySQL tables have a deleted_at column. So, we will check whether the column exists before appending the WHERE clause.

🧠 Full SQL Script

This SQL Server script loops through all MySQL tables and generates the appropriate view creation statements.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      KrishanG
-- Create date: 05/08/2025
-- Procedure:   spDDLCreateAllViews
-- Description: 
--     This procedure dynamically creates views in SQL Server 
--     for each table in a MySQL database via a linked server.
--
--     For every table in the specified MySQL schema:
--       - A corresponding SQL Server view (prefixed with 'lnk_') is created
--         in the target schema (@SQLTargetSchema).
--       - The views use OPENQUERY to directly select data from MySQL.
--       - If the table contains a 'deleted_at' column, a filter 
--         (deleted_at IS NULL) is automatically applied in the view.
--
--     This allows SQL Server to query live data from MySQL as if it were
--     native tables, while filtering out soft-deleted records when applicable.
--
-- Parameters:
--     @LinkedServerName   - The name of the linked server pointing to MySQL.
--     @MySQLDB            - The name of the MySQL database/schema.
--     @SQLTargetSchema    - The schema where the views will be created in SQL Server.
--
-- Notes:
--     - Views are created or altered using CREATE OR ALTER VIEW.
--     - Temporary metadata tables are used to detect available tables and columns.
--     - Make sure the linked server is properly configured before running.
-- =============================================

Create procedure spDDLCreateAllViews(
         @LinkedServerName SYSNAME = 'SB',
         @MySQLDB SYSNAME = 'schoolbox',
         @SQLTargetSchema SYSNAME = 'dbo')
as 

Begin
-- Test block
--DECLARE @LinkedServerName SYSNAME = 'SB';
--DECLARE @MySQLDB SYSNAME = 'schoolbox';
--DECLARE @SQLTargetSchema SYSNAME = 'dbo';
DECLARE @SQL NVARCHAR(MAX);
DECLARE @TableName SYSNAME;

SET NOCOUNT ON;

-- Step 1: Get all table names
SET @SQL = '
IF OBJECT_ID(''MySQLTables'') IS NOT NULL DROP TABLE MySQLTables;

SELECT TABLE_NAME 
INTO MySQLTables
FROM OPENQUERY([' + @LinkedServerName + '], 
    ''
    SELECT TABLE_NAME
    FROM information_schema.tables
    WHERE table_schema = ''''' + @MySQLDB + '''''
      AND table_type = ''''BASE TABLE'''' 
    '')';

EXEC sp_executesql @SQL;

-- Step 2: Get tables that have a deleted_at column
SET @SQL = '
IF OBJECT_ID(''MySQLDeletedAtTables'') IS NOT NULL DROP TABLE MySQLDeletedAtTables;

SELECT DISTINCT TABLE_NAME 
INTO MySQLDeletedAtTables
FROM OPENQUERY([' + @LinkedServerName + '], 
    ''
    SELECT TABLE_NAME
    FROM information_schema.columns
    WHERE table_schema = ''''' + @MySQLDB + '''''
      AND COLUMN_NAME = ''''deleted_at'''' 
    '')';

EXEC sp_executesql @SQL;

-- Step 3: Loop and create views
DECLARE cur CURSOR FOR
SELECT TABLE_NAME FROM MySQLTables;

OPEN cur;
FETCH NEXT FROM cur INTO @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @HasDeletedAt BIT = 0;

    IF EXISTS (SELECT 1 FROM MySQLDeletedAtTables WHERE TABLE_NAME = @TableName)
        SET @HasDeletedAt = 1;

    -- Base query with WHERE 1=1
    SET @SQL = '
CREATE OR ALTER VIEW ' + QUOTENAME(@SQLTargetSchema) + '.' + QUOTENAME('lnk_' + @TableName) + ' AS
SELECT * FROM OPENQUERY([' + @LinkedServerName + '], 
    ''SELECT * FROM ' + @MySQLDB + '.' + @TableName + ' WHERE 1=1';

    -- Conditionally add deleted_at filter
    IF @HasDeletedAt = 1
        SET @SQL += ' AND deleted_at IS NULL';

    SET @SQL += ''');';

    PRINT 'Creating view: ' + @TableName;
    EXEC sp_executesql @SQL;

    FETCH NEXT FROM cur INTO @TableName;
END

CLOSE cur;
DEALLOCATE cur;

end 

go

Comments

Post a Comment

Popular posts from this blog

Removing HTML Tags from Text Using SQL Server User-Defined Function

  Introduction: In this blog post, we'll explore how to create and use a SQL Server User-Defined Function (UDF) to remove HTML tags from a text string. This function can be handy when you need to extract plain text from HTML content stored in your database. Creating the Function: First, let's create the SQL Server UDF named udf_StripHTML . This function takes a VARCHAR(MAX) parameter @HTMLText , which represents the HTML content from which we want to remove the tags. It returns a VARCHAR(MAX) value, representing the text stripped of HTML tags. sql SET QUOTED_IDENTIFIER ON GO  CREATE FUNCTION [dbo].[udf_StripHTML] ( @HTMLText VARCHAR (MAX)) RETURNS VARCHAR (MAX) AS BEGIN DECLARE @Start INT   DECLARE @End INT   DECLARE @Length INT   SET @Start = CHARINDEX( '<' , @HTMLText )  SET @End = CHARINDEX( '>' , @HTMLText , CHARINDEX( '<' , @HTMLText ))  SET @Length = ( @End - @Start ) + 1   WHILE @Start > 0 AND @...
Post a Comment
Read more

Using SSRS web services to render a report as a PDF

I have been looking around the net for some decent code which would explain how I could render a report, using SSRS 2008 web services as a PDF.   The need was to extract reports sitting on a SSRS 2008 server sitting on a NT domain on a trusted network, whereas my web server was sitting in a DMZ. Where the only communication allowed by the network admin was port 80. To do this you will need to use the SSRS2008   ReportExecution2005.asmx web service. This could be accesses using the following URL assuming your SSRS server was installed using the default settings. http://YourServerIP/reportserver/reportexecution2005.asmx?wsdl 1.        Create a user on your AD domain with the least amount of privileges (say ReportUser) 2.        Give this account browse access on the reporting server for the desired reports. 3.        To get this working in visual studio 2010 (I am using t...
1 comment
Read more

Troubleshooting Database Mail: General steps

SQL Server 2008 R2 Other Versions SQL Server "Denali" SQL Server 2008 SQL Server 2005 Troubleshooting Database Mail involves checking the following general areas of the Database Mail system. These procedures are presented in a logical order, but can be evaluated in any order. To determine if Database Mail is enabled In SQL Server Management Studio, connect to an instance of SQL Server by using a query editor window, and then execute the following code: Copy sp_configure 'show advanced', 1; GO RECONFIGURE; GO sp_configure; GO In the results pane, confirm that the run_value for Database Mail XPs is set to 1 . If the run_value is not 1 , Database Mail is not enabled. Database Mail is not automatically enabled to reduce the number of features available for attack by a malicious user. For more information, see Understanding Surface Area Configuration . If you decide that it is appropriate to enable Database Mail, execute the fo...
Post a Comment
Read more