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.