mysql replicates table of across databases in the same IP address example

Data replication classification between database tables

When developing with a database, it is common to import data from one table to another. Of course, you can write programs to implement, but the program often needs a development environment, not convenient. The most convenient is to import directly using the sql language. It is convenient and easy to modify. Here is how to import it.

1. Same table structure and same database (e.g. table1,table2)

Sql:

insert into table1 select   *    from table2 ( Fully replicated )
insert into table1 select   distinct   *   from table2( No duplicate records) 
insert into table1 select   top 5 *   from   table2 ( before 5 A record )
2 And not with 1 In the database (for example, db1 table1,db2 table2)
sql:        
[code]
insert into db1.table1 select   *    from db2.table2 ( Fully replicated )
insert into db1.table1 select   distinct   *   from db2table2( No duplicate records) 
insert into tdb1.able1 select   top 5 *   from   db2table2 ( before 5 A record )

3. Tables with different structure or copied part of 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. Copy the data of dn_uer2 into dn_usertemp

sql:insert into dn_usertemp select * from dn_user2

c. Copy 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

The MySql database replicates table data

Quickly copy the mytbl table in the production database to mytbl_new with two commands as follows:

CREATE TABLE mytbl_new LIKE production.mytbl;
INSERT mytbl_new SELECT * FROM production.mytbl;

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

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

Note: ES73en. mytbl is the name of the database that specifies the table to be replicated as production. It is optional.

Without production., the MySQL database will assume that mytbl is in the database in which it is currently operating.

In addition, the replicated data in THE mysql database is:

select * into desTable from sourceTable in mssql In support, in mysql Do not support 
insert into desTable select * from sourceTable