Synchronizing data for different types of databases

Synchronizing data for different types of databases

The project encountered some data, the data has been reflected in other projects, directly to their table and table data copied down, but in the copy found that I use SQL 2008, they use the MySQL, two different databases, generated statements slightly different, due to lazy, Want to use the most convenient way to synchronize data, on-line search for solutions, reference to achieve, although the online more similar, but after all, it is their own experience, recorded.

1. First open the MySQL interface (this omits the installation configuration and other steps), enter the database you want to connect, verify the link is correct.

2 Verify that the MySQL driver is present, select it if it exists, open Control Panel-"Administrative Tools-" data source (ODBC)

3 Click the Add button

4 If there are similar MySQL driver, then choose, if there is no driver then you need to download a (https://dev.mysql.com/downloads/connector/odbc/) on the official website, to install (step omitted, next line), After installation, repeat the above operation, go to the next interface, choose-"Click to complete

5 after completion will pop up the interface, fill in the information as required-"test, if successful will be prompted."

More than 6 is the MySQL driver and connection settings test, the following is how to operate in the database

7 Open SQL 2008 New Query window as

8 Copy the following code into the Query box, click on the execution (note: @provstr This property in the drive must be the same as the one you just configured "N ' driver={mysql ODBC 5.3 ANSI DRIVER}"), and then the link address

EXEC Master.dbo.sp_addlinkedserver

@server = N ' MYSQL ',

@srvproduct =n ' MySQL ',

@provider =n ' Msdasql ',

@provstr =n ' driver={mysql ODBC 5.3 ANSI DRIVER}; server=192.16.10.101; _

DATABASE=LCJ; User=root; password=111111; Option=3 '

9 execution succeeds, as shown in the SQL 2008 outermost server object:

10 You can create a new library in SQL 2008 (dataabase), the table can not be created, execute the following SQL statement in the MySQL database lcj table stationd Copy to New library

SELECT * into [dataabase].dbo.stationd

From OPENQUERY (MYSQL, ' SELECT * from Lcj.stationd ')

11 This is a statement that replicates tables between different libraries "dhqxzb,dataabase" Under the same SQL Server, by the way.

SELECT * into DHQXZB.dbo.stationd from Dataabase.dbo.stationd

---------------------------Split Line----------------------------------

noun explanation

ODBC: Open Database Connectivity,odbc is created to address data sharing among heterogeneous databases and is now Wosa (the Windows open System The main part of Arhitecture,windows Open system architecture and a database access interface standard based on Windows environment ODBC provides a unified interface for heterogeneous Digital library access, allowing applications to access data managed by different DBMS using SQL as the data Access standard , allowing the application to manipulate data directly in DB without changing with DB. ODBC provides access to DB files on various types of computers, and even non-database objects such as Excel tables and asci i data files

Synchronizing data for different types of databases