SQL跨数据库复制表数据
不同服务器数据库之间的数据操作
不同数据库之间复制表的数据的方法：
当表目标表存在时：
insert into 目的数据库..表 select * from 源数据库..表
当目标表不存在时：
select * into 目的数据库..表 from 源数据库..表
--如果在不同的SQL之间:
insert into openrowset('sqloledb','目的服务器名';'sa';'',目的数据库.dbo.表)
select * from 源数据库..表
--创建链接服务器
exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 '
exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, '用户名 ', '密码 '
--查询示例
select * from ITSV.数据库名.dbo.表名
--导入示例
select * into 表 from ITSV.数据库名.dbo.表名
--以后不再使用时删除链接服务器
exec sp_dropserver 'ITSV ', 'droplogins '
--连接远程/局域网数据(openrowset/openquery/opendatasource)
--1、openrowset
--查询示例
select * from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)
--生成本地表
select * into 表 from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)
--把本地表导入远程表
insert openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)
select *from 本地表
--更新本地表
update b
set b.列A=a.列A
from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)as a inner join 本地表 b
on a.column1=b.column1
--openquery用法需要创建一个连接
--首先创建一个连接创建链接服务器
exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 '
--查询
select *
FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ')
--把本地表导入远程表
insert openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ')
select * from 本地表
--更新本地表
update b
set b.列B=a.列B
FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ') as a
inner join 本地表 b on a.列A=b.列A
--3、opendatasource/openrowset
SELECT *
FROM opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ' ).test.dbo.roy_ta
--把本地表导入远程表
insert opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ').数据库.dbo.表名
select * from
-----------------------------------------------------------------------------------------------------------------------------------
好吧，如果上面看得烦下面有个更容易理解的例子：
Exec sp_droplinkedsrvlogin DBVIP,Null
Exec sp_dropserver DBVIP
EXEC sp_addlinkedserver
@server='DBVIP',--被访问的服务器别名
@srvproduct='',
@provider='SQLOLEDB',
@datasrc='Server2' --要访问的服务器
EXEC sp_addlinkedsrvlogin
'DBVIP', --被访问的服务器别名
'false',
NULL,
'sa', --帐号
'thankyoubobby' --密码
Select * from DBVIP.pubs.dbo.orders
/////////////////////////////ORACLE////////////////////////////
Exec sp_droplinkedsrvlogin demo,Null
Exec sp_dropserver demo
go
EXEC sp_addlinkedserver
@server ='demo',
@srvproduct='Oracle',
@provider='MSDAORA',
@datasrc='ServiceName'
EXEC sp_addlinkedsrvlogin
'demo',
'false',
NULL,
'userid',
'password'
go
SELECT * FROM OPENQUERY(demo ,'select * from tbdemo' )
UPDATE OPENQUERY (demo, 'SELECT id FROM tbdemo WHERE id = 101')
SET name = 'hello';
INSERT OPENQUERY (demo, 'SELECT id FROM tbdemo')
VALUES ('hello');
DELETE OPENQUERY (demo, 'SELECT id FROM tbdemo WHERE name = ''hello''');
-------------------------------------------具体例子------------------------------------------------------------
if exists(select 1 from master.dbo.sysservers where srvname = 'test')
begin
exec sp_droplinkedsrvlogin 'test','sa'
exec sp_dropserver 'test'
end
--建立连接服务器
EXEC sp_addlinkedserver 'test', 'ms','SQLOLEDB', '192.168.1.99'
exec sp_addlinkedsrvlogin 'test','false',null,'sa',''
select * from test.db_film.dbo.T_film
go
if exists(select 1 from master.dbo.sysservers where srvname = 'test')
begin
exec sp_droplinkedsrvlogin 'test','sa'
exec sp_dropserver 'test'
end
go
sql sever跨数据库复制数据的方法
2014-01-09 10:52 by xiashengwang, 1547 阅读, 0 评论, 收藏, 编辑
1，用Opendatasource系统函数
详细的用法已经注释在sql代码中了。这个是在sqlserver到sqlserver之间的倒数据。2005，2008，2012应该都是适用的。
--从远程服务器192.168.66.154上查询100条数据，然后导入到dbo.dquestiondata中
insert into dbo.dquestiondata
select top 100 * from
opendatasource('sqloledb','data source=192.168.6.154;user id=sa;password=xxxxxx').Answer.dbo.DQuestionData
--opendatasource 是一个系统函数，第一个参数是Provider Name，第二个参数是Oledb链接字符串，
--注意连接字符串里没有指定数据库；数据库名称，Schema，表名在opendatasource函数后面书写。
--执行上面的语句会报如下的错，原因是没有开启远程查询支持
--SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries'
--because this component is turned off as part of the security configuration for this server.
--A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure.
--For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.
--解决办法1：在图形界面中，SQL Server 安全配置-->机能的安全配置-->开启远程查询Openrowset和opendatasource的支持
--解决办法2：用sp_confing系统存储过程，以命令行的方式开启
--首先必须开启‘show advanced options’高级选项，因为‘Ad Hoc Distributed Queries’属于高级选项
exec sp_configure 'show advanced options' ,1
reconfigure
--下面这步可以省略，不带参数的sp_configure，表示查看所有可用的选项
--记住上面一定要加reconfigure语句，目的是使选项立即生效，不然我们
--还是看不到'Ad Hoc Distributed Queries'高级选项
exec sp_configure
--打开'Ad Hoc Distributed Queries'选项
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
--记得用完选项后，关闭这些选项
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure
--查看一下是不是关闭了
exec sp_configure
2，用openrowset系统函数
--1,Microsoft OLE DB Provider for SQL Server
--注意第二部分链接字符串的写法很是奇怪Server，user，passwrod是用“；”连接的。
select top 100 * from
openrowset('sqloledb','192.168.6.154';'sa';'xxxxx',Answer.dbo.DQuestionData)
--2,ODBC数据源的方式：Microsoft OLE DB Provider for ODBC Drivers
select top 100 * from
openrowset('MSDASQL','DRIVER={SQL Server};SERVER=192.168.6.154;UID=sa;PWD=xxxxx',Answer.dbo.DQuestionData)
上面的两种方法都会用到oledb提供商的名称，下面的这个系统存储过程可以查看oledb提供程序的名称
--用于查看oledb提供者名称 EXEC master..xp_enum_oledb_providers
3，用链接服务器
如果要多次用到远程查询，每次都写那么长的链接字符串有点麻烦。可以考虑重复利用。
用链接服务器可以很好的解决这个问题
-- 创建链接服务器 exec sp_addlinkedserver 'svr_link','','sqloledb','192.168.6.154' -- 创建登录信息 exec sp_addlinkedsrvlogin 'svr_link','false',null,'sa','xxxx' --查询 格式为：链接服务器.数据库名.架构名.表名 select top 100 * from svr_link.Answer.dbo.DQuestionData -- 删除链接服务器 exec sp_dropserver 'svr_link','droplogins'
需要注意的几点：
1，虽然上面的例子都是从远程服务器select数据到本地，
但我们同样可以将本地的数据导向远程服务器
-- 往远程数据库192.168.6.154的Answer数据库的test表插入两条数据 insert into svr_link.Answer.dbo.test select 1,'zhang' union all select 2,'wang'
2，有了链接服务器，就可以用openquery系统函数了
--检索数据 select * from openquery(svr_link,'select * from Answer.dbo.test') -- 插入数据 insert into openquery(svr_link,'select * from Answer.dbo.test') select 3,'li'
