首页 > 代码库 > SQL跨数据库复制表数据
SQL跨数据库复制表数据
Insert是T-sql中常用语句，Insert INTO table(field1,field2,...) values(value1,value2,...)这种形式的在应用程序开发中必不可少。但我们在开发、测试过程中，经常会遇到需要表复制的情况，如将一个table1的数据的部分字段复制到table2中，或者将整个table1复制到table2中，这时候我们就要使用SELECT INTO 和 INSERT INTO SELECT 表复制语句了。
1.INSERT INTO SELECT语句
语句形式为：Insert into Table2(field1,field2,...) select value1,value2,... from Table1
要求目标表Table2必须存在，由于目标表Table2已经存在，所以我们除了插入源表Table1的字段外，还可以插入常量。示例如下：
--1.创建测试表 create TABLE Table1 ( a varchar(10), b varchar(10), c varchar(10), CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED ( a ASC ) ) ON [PRIMARY] create TABLE Table2 ( a varchar(10), c varchar(10), d int, CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED ( a ASC ) ) ON [PRIMARY] GO --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 * from Table2 --3.INSERT INTO SELECT语句复制表数据 Insert into Table2(a, c, d) select a,c,5 from Table1 GO --4.显示更新后的结果 select * from Table2 GO --5.删除测试表 drop TABLE Table1 drop TABLE Table2
2.SELECT INTO FROM语句
语句形式为：SELECT vale1, value2 into Table2 from Table1
要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中。示例如下：
--1.创建测试表 create TABLE Table1 ( a varchar(10), b varchar(10), c varchar(10), CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED ( a ASC ) ) ON [PRIMARY] GO --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 from Table1 GO --4.显示更新后的结果 select * from Table2 GO --5.删除测试表 drop TABLE Table1 drop TABLE Table2
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
/**1.开启Ad Hoc Distributed Queries组件，在sql查询编辑器中执行如下语句：exec sp_configure ‘show advanced options‘,1reconfigureexec sp_configure ‘Ad Hoc Distributed Queries‘,1reconfigure2.关闭Ad Hoc Distributed Queries组件，在sql查询编辑器中执行如下语句：exec sp_configure ‘Ad Hoc Distributed Queries‘,0reconfigureexec sp_configure ‘show advanced options‘,0reconfigure*/--网络查询select * from openrowset( ‘SQLOLEDB‘, ‘192.168.0.245‘; ‘sa‘; ‘mima‘,tm_demo_sys.dbo.batch_info) where moduleAlias = ‘batch_DataCJRWSB‘ order by sortselect * from openrowset( ‘SQLOLEDB‘, ‘192.168.0.245‘; ‘sa‘; ‘mima‘,tm_demo_sys.dbo.batch_infoItem) where moduleAlias = ‘batch_DataCJRWSB‘ order by sort--本地查询select * from batch_Info where moduleAlias = ‘batch_DataCJRWSB‘order by sortselect * from batch_InfoItem where moduleAlias = ‘batch_DataCJRWSB‘order by sort--本地数据库导入到 网络数据库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 by sortinsert 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之间: 8 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, ‘用户名 ‘, ‘密码 ‘ 15 16 --查询示例 17 select * from ITSV.数据库名.dbo.表名 18 19 --导入示例 20 select * into 表 from ITSV.数据库名.dbo.表名 21 22 --以后不再使用时删除链接服务器 23 exec sp_dropserver ‘ITSV ‘, ‘droplogins ‘ 24 25 --连接远程/局域网数据(openrowset/openquery/opendatasource) 26 --1、openrowset 27 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 update b 40 set b.列A=a.列A 41 from openrowset( ‘SQLOLEDB ‘, ‘sql服务器名 ‘; ‘用户名 ‘; ‘密码 ‘,数据库名.dbo.表名)as a inner join 本地表 b 42 on a.column1=b.column1 43 44 --openquery用法需要创建一个连接 45 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 update b 56 set b.列B=a.列B 57 FROM openquery(ITSV, ‘SELECT * FROM 数据库.dbo.表名 ‘) as a 58 inner join 本地表 b on a.列A=b.列A 59 60 --3、opendatasource/openrowset 61 SELECT * 62 FROM opendatasource( ‘SQLOLEDB ‘, ‘Data Source=ip/ServerName;User ID=登陆名;Password=密码 ‘ ).test.dbo.roy_ta 63 --把本地表导入远程表 64 insert opendatasource( ‘SQLOLEDB ‘, ‘Data Source=ip/ServerName;User ID=登陆名;Password=密码 ‘).数据库.dbo.表名 65 select * from 66 ----------------------------------------------------------------------------------------------------------------------------------- 67 好吧，如果上面看得烦下面有个更容易理解的例子： 68 Exec sp_droplinkedsrvlogin DBVIP,Null 69 Exec sp_dropserver DBVIP 70 EXEC sp_addlinkedserver 71 @server=‘DBVIP‘,--被访问的服务器别名 72 @srvproduct=‘‘, 73 @provider=‘SQLOLEDB‘, 74 @datasrc=‘Server2‘ --要访问的服务器 75 76 EXEC sp_addlinkedsrvlogin 77 ‘DBVIP‘, --被访问的服务器别名 78 ‘false‘, 79 NULL, 80 ‘sa‘, --帐号 81 ‘thankyoubobby‘ --密码 82 83 Select * from DBVIP.pubs.dbo.orders 84 85 86 /////////////////////////////ORACLE//////////////////////////// 87 Exec sp_droplinkedsrvlogin demo,Null 88 Exec sp_dropserver demo 89 go 90 EXEC sp_addlinkedserver 91 @server =‘demo‘, 92 @srvproduct=‘Oracle‘, 93 @provider=‘MSDAORA‘, 94 @datasrc=‘ServiceName‘ 95 96 EXEC sp_addlinkedsrvlogin 97 ‘demo‘, 98 ‘false‘, 99 NULL, 100 ‘userid‘, 101 ‘password‘ 102 go103 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 * from test.db_film.dbo.T_film120 go121 if exists(select 1 from master.dbo.sysservers where srvname = ‘test‘) 122 begin 123 exec sp_droplinkedsrvlogin ‘test‘,‘sa‘ 124 exec sp_dropserver ‘test‘ 125 end 126 go
SQL跨数据库复制表数据
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。