Replicating MSSQL to MySQL

ok well its not really “Replication”, its keeping a MySQL table in sync with MSSQL using a SQL Job.

Whilst great for small, frequent, data updates, this method (using a linked-server) is not suitable for bulk data movement, where SSIS, BCP, or SQLCDM would be much faster.

Job Step 1 Remove old data from MySQL

USE SomeDatabase;
GO

DELETE FROM OPENQUERY
            (SomeMySQLInstance,
             'SELECT ID, Code, Comments FROM SomeMySqlTable'
            )
WHERE ID NOT IN
      (
          SELECT ID
          FROM [SomeServer].[dbo].[SomeView]
      );

PRINT 'Rows Deleted = ' + CONVERT(varchar(10), @@rowcount)
 

Job Step 2 Copy new data to MySQL

USE SomeDatabase;
GO

INSERT OPENQUERY
       (SomeMySqlInstance,
        'SELECT ID, Code, Comments FROM SomeMySqlTable'
       )
SELECT *
FROM [SomeDatabase].[dbo].[SomeView]
WHERE ID NOT IN
      (
          SELECT ID FROM SomeMySqlInstance...SomeMySqlTable
      );

PRINT 'Rows Inserted = ' + CONVERT(varchar(10), @@rowcount)
 

Just to decode that a bit …

  • Step2 Line12:”SomeMySqlInstance…SomeMySqlTable” refers to a Linked Server connection (called “SomeMySqlInstance”), where the default database is the one we want (containing the table “SomeMySqlTable”).
  • Lines 11 & 9: “[SomeView]” is the source of the current data that the MySQL table needs to be synced with. It is just a shortcut way to not need to save a stored-proc output to a table for consumption by OPENQUERY. Note that the view needs a unique ID column.
  • The PRINT statements are just for the jobs history.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s