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

Mysql cross-Database copy table (in the same IP address) example bitsCN.com

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)

SQL:

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)

SQL:
[Code]
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

) ON [PRIMARY]

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

SQL:

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

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
End


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

BitsCN.com