mysql跨数据库复制_SQL跨数据库复制表数据

Insert是T-sql中常用语句，Insert INTO table(field1,field2,...) values(value1,value2,...)这种形式的在应用程序开发中必不可少。但我们在开发、测试过程中，经常会遇到需要表复制的情况，如将一个table1的数据的部分字段复制到table2中，或者将整个table1复制到table2中，这时候我们就要使用SELECT INTO 和 I

吃老虎饺子机

1429人浏览 · 2021-01-18 21:22:46

吃老虎饺子机 · 2021-01-18 21:22:46 发布

1.INSERT INTO SELECT语句

语句形式为：Insert into Table2(field1,field2,...) select value1,value2,... from Table1

要求目标表Table2必须存在，由于目标表Table2已经存在，所以我们除了插入源表Table1的字段外，还可以插入常量。示例如下：

--1.创建测试表

create TABLETable1

(

avarchar(10),

bvarchar(10),

cvarchar(10),CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED(

aASC)

)ON [PRIMARY]

create TABLETable2

(

avarchar(10),

cvarchar(10),

dint,CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED(

aASC)

)ON [PRIMARY]

--2.创建测试数据

Insert into Table1 values('赵','asds','90')Insert into Table1 values('钱','asds','100')Insert into Table1 values('孙','asds','80')Insert into Table1 values('李','asds',null)GO

select * fromTable2--3.INSERT INTO SELECT语句复制表数据

Insert into Table2(a, c, d) select a,c,5 fromTable1GO

--4.显示更新后的结果

select * fromTable2GO

--5.删除测试表

drop TABLETable1drop TABLE Table2

INSERT INTO SELECT语句复制表数据

2.SELECT INTO FROM语句

语句形式为：SELECT vale1, value2 into Table2 from Table1

要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中。示例如下：

--1.创建测试表

create TABLETable1

(

avarchar(10),

bvarchar(10),

cvarchar(10),CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED(

aASC)

)ON [PRIMARY]

--2.创建测试数据

Insert into Table1 values('赵','asds','90')Insert into Table1 values('钱','asds','100')Insert into Table1 values('孙','asds','80')Insert into Table1 values('李','asds',null)GO

--3.SELECT INTO FROM语句创建表Table2并复制数据

select a,c INTO Table2 fromTable1GO

--4.显示更新后的结果

select * fromTable2GO

--5.删除测试表

drop TABLETable1drop TABLE Table2

SELECT INTO FROM创建表并复制表数据

------------------------------------------------------------------------------------------------------------------------

/**

1.开启Ad Hoc Distributed Queries组件，在sql查询编辑器中执行如下语句：

exec sp_configure 'show advanced options',1

reconfigure

exec sp_configure 'Ad Hoc Distributed Queries',1

reconfigure

2.关闭Ad Hoc Distributed Queries组件，在sql查询编辑器中执行如下语句：

exec sp_configure 'Ad Hoc Distributed Queries',0

reconfigure

exec sp_configure 'show advanced options',0

reconfigure*/

--网络查询

select * from openrowset( 'SQLOLEDB', '192.168.0.245'; 'sa'; 'mima',tm_demo_sys.dbo.batch_info) where moduleAlias = 'batch_DataCJRWSB' order bysortselect * from openrowset( 'SQLOLEDB', '192.168.0.245'; 'sa'; 'mima',tm_demo_sys.dbo.batch_infoItem) where moduleAlias = 'batch_DataCJRWSB' order bysort--本地查询

select * from batch_Info where moduleAlias = 'batch_DataCJRWSB'

order bysortselect * from batch_InfoItem where moduleAlias = 'batch_DataCJRWSB'

order bysort--本地数据库导入到网络数据库

insert into openrowset( 'SQLOLEDB', '192.168.0.245'; 'sa'; 'mima',tm_demo_sys.dbo.batch_info)select * from batch_Info where moduleAlias = 'batch_DataCJRWSB' order bysortinsert into openrowset( 'SQLOLEDB', '192.168.0.245'; 'sa'; 'mima',tm_demo_sys.dbo.batch_InfoItem)select * from batch_InfoItem where moduleAlias = 'batch_DataCJRWSB' order by sort

------------------------------------------------------------------------------------------------------------------------

1 不同服务器数据库之间的数据操作2 不同数据库之间复制表的数据的方法：3 当表目标表存在时：4 insert into 目的数据库..表 select * from源数据库..表5 当目标表不存在时：6 select * into 目的数据库..表 from源数据库..表7 --如果在不同的SQL之间:

9 insert into openrowset('sqloledb','目的服务器名';'sa';'',目的数据库.dbo.表)10 select * from源数据库..表11

12 --创建链接服务器

13 exec sp_addlinkedserver 'ITSV', ' ', 'SQLOLEDB', '远程服务器名或ip地址'

14 exec sp_addlinkedsrvlogin 'ITSV', 'false',null, '用户名', '密码'

16 --查询示例

17 select * fromITSV.数据库名.dbo.表名18

19 --导入示例

20 select * into 表 fromITSV.数据库名.dbo.表名21

22 --以后不再使用时删除链接服务器

23 exec sp_dropserver 'ITSV', 'droplogins'

25 --连接远程/局域网数据(openrowset/openquery/opendatasource)

26 --1、openrowset

28 --查询示例

29 select * from openrowset( 'SQLOLEDB', 'sql服务器名'; '用户名'; '密码',数据库名.dbo.表名)30

31 --生成本地表

32 select * into 表 from openrowset( 'SQLOLEDB', 'sql服务器名'; '用户名'; '密码',数据库名.dbo.表名)33

34 --把本地表导入远程表

35 insert openrowset( 'SQLOLEDB', 'sql服务器名'; '用户名'; '密码',数据库名.dbo.表名)36 select *from本地表37

38 --更新本地表

39 updateb40 set b.列A=a.列A41 from openrowset( 'SQLOLEDB', 'sql服务器名'; '用户名'; '密码',数据库名.dbo.表名)as a inner join本地表 b42 on a.column1=b.column143

44 --openquery用法需要创建一个连接

46 --首先创建一个连接创建链接服务器

47 exec sp_addlinkedserver 'ITSV', ' ', 'SQLOLEDB', '远程服务器名或ip地址'

48 --查询

49 select *

50 FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名')51 --把本地表导入远程表

52 insert openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名')53 select * from本地表54 --更新本地表

55 updateb56 set b.列B=a.列B57 FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名') asa58 inner join 本地表 b on a.列A=b.列A59

60 --3、opendatasource/openrowset

61 SELECT *

62 FROM opendatasource( 'SQLOLEDB', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码').test.dbo.roy_ta63 --把本地表导入远程表

64 insert opendatasource( 'SQLOLEDB', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码').数据库.dbo.表名65 select * from

66 -----------------------------------------------------------------------------------------------------------------------------------

67 好吧，如果上面看得烦下面有个更容易理解的例子：68 Exec sp_droplinkedsrvlogin DBVIP,Null

69 Execsp_dropserver DBVIP70 EXECsp_addlinkedserver71 @server='DBVIP',--被访问的服务器别名

72 @srvproduct='',73 @provider='SQLOLEDB',74 @datasrc='Server2' --要访问的服务器

76 EXECsp_addlinkedsrvlogin77 'DBVIP', --被访问的服务器别名

78 'false',79 NULL,80 'sa', --帐号

81 'thankyoubobby' --密码

83 Select * fromDBVIP.pubs.dbo.orders84

86 /////////////////////////////ORACLE////////////////////////////

87 Exec sp_droplinkedsrvlogin demo,Null

88 Execsp_dropserver demo89 go

90 EXECsp_addlinkedserver91 @server ='demo',92 @srvproduct='Oracle',93 @provider='MSDAORA',94 @datasrc='ServiceName'

96 EXECsp_addlinkedsrvlogin97 'demo',98 'false',99 NULL,100 'userid',101 'password'

102 go

103 SELECT * FROM OPENQUERY(demo ,'select * from tbdemo')104 UPDATE OPENQUERY (demo, 'SELECT id FROM tbdemo WHERE id = 101')105 SET name = 'hello';106 INSERT OPENQUERY (demo, 'SELECT id FROM tbdemo')107 VALUES ('hello');108 DELETE OPENQUERY (demo, 'SELECT id FROM tbdemo WHERE name =''hello''');109 -------------------------------------------具体例子------------------------------------------------------------

110 if exists(select 1 from master.dbo.sysservers where srvname = 'test')111 begin

112 exec sp_droplinkedsrvlogin 'test','sa'

113 exec sp_dropserver 'test'

114 end

115 --建立连接服务器

116 EXEC sp_addlinkedserver 'test', 'ms','SQLOLEDB', '192.168.1.99'

117

118 exec sp_addlinkedsrvlogin 'test','false',null,'sa',''

119 select * fromtest.db_film.dbo.T_film120 go

121 if exists(select 1 from master.dbo.sysservers where srvname = 'test')122 begin

123 exec sp_droplinkedsrvlogin 'test','sa'

124 exec sp_dropserver 'test'