SQLServer Data Migration using Linked Server

Migrating data to the SQL Server from any other database can be a bit tedious. There is a quick way that you can use to migrate large chunks of data very quickly. The sample here deals with MySQL, however, the data can be imported using any data source which has an ODBC driver available.

The other limitation of using the Linked server to import data is that it does not create any constraints or indexes. This is purely a data import mechanism and all the other constraints or indexes have to be created once the data import is successful.

--  Migrating the data from MySQL to the SQL Server

-- This is a step by steps guide to import data from a MySQL database to
-- the SQLServer database
--
-- STEP 1:    Create a new Database

-- STEP 2:    Create a linked server using the following command
--            This command is creating a linked server to the test
--            database on server.ip.address.

EXEC master.dbo.sp_addlinkedserver
@server = N'MYSQL10Test', @srvproduct=N'MySQL',
@provider=N'MSDASQL',
@provstr=N'DRIVER={MySQL ODBC 5.1 Driver}; SERVER=server.ip.address;
DATABASE=test; USER=root; PASSWORD=****; OPTION=3'


-- STEP 3:    Import the data from the linked server using
--            the following command one table at a time.

select * into some_table
from openquery(MYSQL10Test, 'select * from test.some_table')


-- STEP 4:    Once all the desired tables are imported, create the
--            Indexes, checks, constraints, keys. These steps only
--            import the data and thus we need to create all the keys
--            and constraints manyally.

-- STEP 5:    Copy over the Stored Proceedures if any.


-- NOTE:
--      These steps are only for importing data from MySQL database to the
--      SQLServer datbase. The resulting tables then need to be modified to
--      create keys, constraints, triggers, etc.
--      Similarly all the functions and stored proceedures are needed to be
--      created in the database.

-- PROBLEMS:
--      Potential problems are mainly due to the incompatible data types. Check
--      the MySQL database for any incorrect or incompatible data.
--      NULLs are treated differently by MySQL and SQLServer. This may create
--      problems during the data transfer.
--      [SQL Server] Unidentified Error: This strange error message comes up
--      when SQLServer fails to identify the problem. Mostly the root cause is
--      incompatible data in the source table.