Mysql cross-database replication table (in the same IP address) example _ MySQL

Source: Internet
Author: User
Mysql cross-Database copy table (in the same IP address) example

Data Replication classification between database tables

When using database development, data in some tables is often imported to each other. Of course, you can write programs for implementation. However, programs often require the development environment, which is inconvenient. It is most convenient to directly import data using the SQL language. It is both convenient and easy to modify. The following is the import method.

1. tables with the same table structure and in the same database (for example, table1, table2)


Insert into table1 select * from table2 (full copy)
Insert into table1 select distinct * from table2 (do not copy duplicate records)
Insert into table1 select top 5 * from table2 (first five records)

2. not in the same database (for example, db1 table1, db2 table2)

Insert into db1.table1 select * from db2.table2 (full copy)
Insert into db1.table1 select distinct * from db2table2 (record duplication is not copied)
Insert into tdb1.able1 select top 5 * from db2table2 (first five records)

3. tables with different table structures or copying some records (for example, dn_user, dn_user2)

A. create a new table [DN_UserTemp] (add a column to the old table dn_user)

Create table [DN_UserTemp] ([Num] [numeric] (18, 0) IDENTITY (1, 1) not null)
[Id] [idtype] not null,
[Name] [fntype] not null,
[Descript] [dstype] NULL,
[LogonNm] [idtype] not null,
[Password] [idtype] NULL,
[Gender] [char] (1) NULL,
[Quited] [booltype] not null,
[OffDuty] [booltype] not null,
[Stopped] [booltype] not null,
[OSBind] [booltype] not null,
[Domain] [idtype] NULL,
[EMail] [fntype] NULL,
[UnitId] [idtype] NULL,
[BranchId] [idtype] NULL,
[DutyId] [idtype] NULL,
[LevelId] [idtype] NULL,
[ClassId] [idtype] NULL,
[TypeId] [idtype] NULL,
[IP] [varchar] (15) COLLATE Chinese_PRC_CI_AS NULL,
[ExpireDT] [datetime] NULL,
[Sort] [int] not null,
[AllowDel] [booltype] not null,
[UnitChief] [booltype] not null,
[BranchChief] [booltype] not null,
[UnitDeputy] [booltype] not null,
[BranchDeputy] [booltype] not null,

[Num] [numeric] (18, 0) IDENTITY (1, 1) NOT NULL


B. upload the data of dn_uer2 to dn_usertemp.

SQL: insert into dn_usertemp select * from dn_user2

C. run dn_usertemp into dn_user


Declare @ I int
Declare @ j int
Declare @ Name fntype
Set @ I = 1
Select @ j = count (*) from dn_usertemp
While @ I <@ j 1

Select @ Name = Name from dn_usertemp where Num = @ I
Print @ Name
Insert into dn_user (Name) values (@ Name) where Num = @ I
Select @ I = @ I 1

Copy table data from a MySql database

Copy the mytbl table in the production database to mytbl_new quickly. the two commands are as follows:

Create table mytbl_new LIKE production. mytbl;
INSERT mytbl_new SELECT * FROM production. mytbl;

The first command is to create a new data table mytbl_new and copy the data table structure of mytbl.

The second command is to copy data from the data table mytbl to the new table mytbl_new.

Note: production. mytbl indicates that the database name of the table to be copied is production. It is optional.

If there is no production function, the MySQL database assumes that mytbl is in the database currently operated.

In addition, copy data in the mysql database as follows:

Select * into desTable from sourceTable supported in mssql, not in mysql
Insert into desTable select * from sourceTable

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.